Stored Procedure Snowflake - time formatting issue

Question

var get_end_time = "select CURRENT_TIME()";
var time_sql = {sqlText: get_end_time};
var time_create = snowflake.createStatement(time_sql);
var time_exec_end = time_create.execute();
time_exec_end.next();
var end_time = time_exec_end.getColumnValue(1);

I have this code above in a JavaScript stored procedure in snowflake, this query produces: 11:27:35.181000000

How can I strip this to only give me 11:27:35?

Answer 1

JavaScript Procedure is returning type as string, so value cannot be interpreted in snowflake as time or to apply time-base formats.

Code without any formatting for time -

CREATE OR REPLACE PROCEDURE test_time()
RETURNS STRING
LANGUAGE javascript
AS
$$
 var get_end_time = "select CURRENT_TIME()";
 var time_sql = {sqlText: get_end_time};
 var time_create = snowflake.createStatement(time_sql);
 var time_exec_end = time_create.execute();
 time_exec_end.next();
 var end_time = time_exec_end.getColumnValue(1);
  return end_time;
$$
;


call test_time();

TEST_TIME
06:20:47.250000000

Use TO_CHAR to get desired format and return same.

CREATE OR REPLACE PROCEDURE test_time()
RETURNS STRING
LANGUAGE javascript
AS
$$
 var get_end_time = "select to_char(CURRENT_TIME())";
 var time_sql = {sqlText: get_end_time};
 var time_create = snowflake.createStatement(time_sql);
 var time_exec_end = time_create.execute();
 time_exec_end.next();
 var end_time = time_exec_end.getColumnValue(1);
  return end_time;
$$
;

call test_time();

TEST_TIME
06:27:50

JavaScript does not have a date data-type.

If we are using SQL based procedure which returns datetype date/timestamp, then its interpretation is as per set format.

In below code there are no format functions applied.

create or replace procedure test_time()
returns date
language sql as
$$
begin
  return (select current_timestamp());
end;
$$
;

alter session set timestamp_output_format='hh24:mi:ss.sss';

call test_time();

TEST_TIME
07:03:48.48S

alter session set timestamp_output_format='hh24:mi:ss.ff';

call test_time();

TEST_TIME
07:05:07.236000000

alter session set timestamp_output_format='hh24:mi:ss';

call test_time();

TEST_TIME
07:05:39