Getting timepart from timestamp

Question

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

Answer 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

Answer 2

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;