Snowflake/javascript datetime format changes during iteration

Question

 Column_name   DateType           Actual data(in table)   
 CALDT         TIMESTAMP_LTZ(9)   2021-12-07 15:17:04.673 -0800

Javascript converts above date into something like this 'Tue Dec 07 2021 14:52:12 GMT-0800 (Pacific Standard Time)'

Here is the javascript code

         var caldt = " SELECT CALDT"
         caldt += "  FROM INFORMATION_SCHEMA.TABLES "
         caldt += " WHERE TABLE_SCHEMA = " + String.fromCharCode(39)  + TARGET_SCHEMA + String.fromCharCode(39)
         caldt += "   AND TABLE_NAME   = " + String.fromCharCode(39)  +  TARGET_TABLE + String.fromCharCode(39);
                 
    var cal_statement = snowflake.createStatement({sqlText: caldt });
    var cal_resultSet = cal_statement.execute();
    var cal_column_name_array = "";    
      
    while (cal_resultSet .next()) {
        var cal_column_name =cal_resultSet .getColumnValue(1);
       **cal_column_name_array = cal_column_name_array += String.fromCharCode(39) + cal_column_name + String.fromCharCode(39) + ", "    ;        
    }
 cal_column_name_array = cal_column_name_array.substring(0, cal_column_name_array.length-2);** 

Above code between ** ... ** is modifying the timestamp

however i need to get same datetime stamp(2021-12-07 15:17:04.673 -0800) and

NOT 'Tue Dec 07 2021 14:52:12 GMT-0800 (Pacific Standard Time)'

How to format the javascript iteration code to return datetime/time stamp. Thx

Answer 1

Simplified procedure that returns the date formatted in ISO format:

create or replace procedure date_format()
returns varchar not null
language javascript
as
$$
var caldt = "select current_timestamp() x;"
var cal_statement = snowflake.createStatement({sqlText: caldt });
var cal_resultSet = cal_statement.execute();

while (cal_resultSet.next()) {
    return cal_resultSet.getColumnValue(1).toISOString();
}
$$;

call date_format();

That returns 2021-12-08T05:04:26.000Z by using .toISOString().

The answer at https://stackoverflow.com/a/13219636/132438 includes extra code for additional formatting:

new Date().toISOString().replace(/T/, ' ').replace(/\..+/, '')

returns 2012-11-04 14:55:45.

Answer 2

I have tested, if you force the timestamp column in your Snowflake query to be string, then it will avoid your issue.

See test case below.

1. Prepare the data (note that the timestamp column needs to be TIMESTAMP_TZ type, otherwise it will NOT preserve the original string as it gets converted to either local timezone or no timezone, depending on your setting):

create or replace table date_table(
    date_string string, 
    date_time_tz timestamp_tz,
    date_time_ntz timestamp_ntz,
    date_time_ltz timestamp_ltz
);

insert into date_table values 
    ('2021-12-07 15:17:04.673 -0800', 
     '2021-12-07 15:17:04.673 -0800',
     '2021-12-07 15:17:04.673 -0800',
     '2021-12-07 15:17:04.673 -0800');

2. prepare the SP:

create or replace procedure test_date(query string)
returns string
language javascript
as 
$$
    var my_date = "";
    var cal_statement = snowflake.createStatement({sqlText: QUERY });
    var rs = cal_statement.execute();
    while (rs.next()) {
        
        my_date += '*' + rs.getColumnValue(1) + '*\n';
        my_date += '*' + rs.getColumnValue(2) + '*\n';
        my_date += '*' + rs.getColumnValue(3) + '*\n';
        my_date += '*' + rs.getColumnValue(4) + '*';
    }
    
    return my_date;
$$;

3. call the SP using timestamp value (it will show your issue):

call test_date('select * from date_table');

+------------------------------------------------------------------+
| TEST_DATE                                                        |
|------------------------------------------------------------------|
| *2021-12-07 15:17:04.673 -0800*                                  |
| *Tue Dec 07 2021 23:17:04 GMT+0000 (Coordinated Universal Time)* |
| *Tue Dec 07 2021 15:17:04 GMT+0000 (Coordinated Universal Time)* |
| *Tue Dec 07 2021 23:17:04 GMT+0000 (Coordinated Universal Time)* |
+------------------------------------------------------------------+

4. call the SP by casting timestamp value to STRING (what you wanted):

call test_date('select date_string, date_time_tz::string, date_time_ntz::string, date_time_ltz::string from date_table');

+---------------------------------+
| TEST_DATE                       |
|---------------------------------|
| *2021-12-07 15:17:04.673 -0800* |
| *2021-12-07 15:17:04.673 -0800* |
| *2021-12-07 15:17:04.673000000* |
| *2021-12-07 23:17:04.673 Z*     |
+---------------------------------+

So you need to make sure you use timestamp_tz data type and cast to string, it should help to resolve your issue.

UPDATE

We can use getColumnValueAsString() instead of getColumnValue() of the ResultSet object to cast the value from TIMESTAMP to STRING inside the JS, rather than at the SQL level.

https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html#getColumnValueAsString

So updated the SP is below:

create or replace procedure test_date(query string)
returns string
language javascript
as 
$$
    var my_date = "";
    var cal_statement = snowflake.createStatement({sqlText: QUERY });
    var rs = cal_statement.execute();
    while (rs.next()) {
        
        my_date += '*' + rs.getColumnValueAsString(1) + '*\n';
        my_date += '*' + rs.getColumnValueAsString(2) + '*\n';
        my_date += '*' + rs.getColumnValueAsString(3) + '*\n';
        my_date += '*' + rs.getColumnValueAsString(4) + '*';
    }
    
    return my_date;
$$;

And then we can just run SELECT *:

call test_date('select * from date_table');

+----------------------------------------+
| TEST_DATE                              |
|----------------------------------------|
| *2021-12-07 15:17:04.673 -0800*        |
| *2021-12-07 15:17:04.673000000 -08:00* |
| *2021-12-07 15:17:04.673000000*        |
| *2021-12-07 23:17:04.673000000*        |
+----------------------------------------+

I do not know what controls the number of digits in the milliseconds part, as the TIMESTAMP_%_OUTPUT_FORMAT parameters can't seem to control it.

You might need to manually get rid of the trailing zeros if you have to.