TO_CHAR and SSSS (hours past midnight)

Question

In converting some code from Netezza to Snowflake, I've come across what seems to be a gap in Snowflake.

The format is:

TO_CHAR(<date/time field>,'SSSS')

The parameter SSSS is defined as Seconds past midnight.

https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html

Snowflake cannot extract time below the second level, e.g. millisecond, microsecond, nanosecond.

Am I missing something? Other alternatives?

Answer 1

What is interesting is that this question is asking for sub-second timeframes, but making reference to a function that only returns at the seconds-level for Netezza. So, I'm not sure exactly what you are looking for, but this is where you'd go with Snowflake, I believe, using a current_timestamp() as an example:

SET timecheck = current_timestamp();

SELECT DATEDIFF(s,date_trunc(day,$timecheck),$timecheck);

This returns time since midnight in seconds.

SELECT DATEDIFF(ms,date_trunc(day,$timecheck),$timecheck);

This returns time since midnight in milliseconds.

One could easily make this into a function, as well, if you wanted to make the call a bit more concise in your code.

Answer 2

Thank you for the above answer. It solves the issue I was facing.

select top 10 current_timestamp() ,date_trunc(day,convert_timezone('UTC','America/Chicago',mmt.TRANSACTION_DATE::timestamp_ntz)) ,mmt.TRANSACTION_DATE "in_GMT/UTC" ,convert_timezone('UTC','America/Chicago',mmt.TRANSACTION_DATE::timestamp_ntz) "in_CDT" ,DATEDIFF(seconds, convert_timezone('UTC','America/Chicago',mmt.TRANSACTION_DATE::timestamp_ntz), date_trunc(day,current_timestamp())) From test_table mmt;