2

Hello my little snowflakes, how can I isolate the correct time part of a timestamp?

SELECT CURRENT_TIMESTAMP()                                                                                                                   AS a,
       CURRENT_TIMESTAMP() :: timestamp_ntz                                                                                                  AS b,
       CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)                                                                                            AS c,
       TO_TIME(CURRENT_TIMESTAMP)                                                                                                            AS d,
       TIME_FROM_PARTS(DATE_PART(hour, CURRENT_TIMESTAMP()), DATE_PART(minute, CURRENT_TIMESTAMP()), DATE_PART(second, CURRENT_TIMESTAMP())) AS e 

Produces

A                   B                   C                   D        E        
------------------- ------------------- ------------------- -------- -------- 
2017-06-11 20:33:00 2017-06-11 18:33:00 2017-06-11 20:33:00 12:33:00 12:33:00 

I just want 20:33:00

0

Try

alter session set time_output_format = 'HH24:MI:SS.FF';

From the docs, but my guess is that it should be the default.

If you haven't started writing an app with snowflake, it reads like a lot of a smoke. You may want to try PostgreSQL and see if it meets your needs first.

If that doesn't work, you may want to try.. to_timestamp

SELECT to_timestamp(CURRENT_TIMESTAMP(), 'HH24:MI:SS.FF') AS a;
  • Thanks for the suggestion; already tried with no success. I don't have a choice with the DB here. – Kermit Jun 12 '17 at 1:56
  • You may want to tell us what you've tried. This is the first question on Snowflake. So I'm glad you've opened the network up to it, however I'm hesitant to state we have the expertise to answer this. Check out my update. – Evan Carroll Jun 12 '17 at 2:05
  • It looks like it might be a JDBC issue. The session parameters are identical to the console, but client produces different results than the console (correctly). I've reached out to support and will update the question when I receive a response. – Kermit Jun 12 '17 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.