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

1

Running SHOW PARAMETERS LIKE 'TIMEZONE'; returns in my case:

key         value               default                
TIMEZONE    America/Los_Angeles America/Los_Angeles 

SELECT CURRENT_TIMESTAMP() results in 2019-11-04 07:48:47.289 -0800.

Then I can alter the session parameter like this ALTER SESSION SET TIMEZONE = 'Europe/Amsterdam';

Now SHOW PARAMETERS LIKE 'TIMEZONE'; gives:

key         value               default 
TIMEZONE    Europe/Amsterdam    America/Los_Angeles

And SELECT CURRENT_TIMESTAMP() results in 2019-11-04 16:49:45.987 +0100

That hopefully shows the logic behind CURRENT_TIMESTAMP.

Answer to actual question: To get the timepart simply use TIMESTAMP::TIME

SELECT 
CURRENT_TIMESTAMP() :: TIME AS Amsterdam_Time,
CONVERT_TIMEZONE('America/Los_Angeles', CURRENT_TIMESTAMP)::TIME AS LA_Time,
CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)::TIME AS UTC_Time;

Results in (with have Amsterdam as default):

AMSTERDAM_TIME  LA_TIME     UTC_TIME
16:55:37        07:55:37    15:55:37
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.