TIMEZONE

nan
New Contributor II

‎02-26-2024 09:57 AM

Can I get some help from Databricks to help me understand how those timestamps being interpreted? Some are really confusing me. I have timestamp coming into AWS Databricks as String type. And the string timestamp is represented in UTC. I ran below query in both UTC and CST cluster and I am trying to find the correct way to interpret the time. Thanks.select 'xxxx_cluster' as Cluster, EVENTTIME AS EVENTTIME_raw_string , to_timestamp(EVENTTIME) as test_1 , to_timestamp_ntz(EVENTTIME) as test_2 -- no timezone , to_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC') as test_3 , to_timestamp(to_timestamp(EVENTTIME), 'Etc/UTC') as test_33 , to_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago') as test_4 , to_timestamp(to_timestamp(EVENTTIME), 'America/Chicago') as test_44 , to_utc_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC') as test_5 , to_utc_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago') as test_6 , to_utc_timestamp(to_timestamp(EVENTTIME), 'Etc/UTC') as test_55 , to_utc_timestamp(to_timestamp(EVENTTIME), 'America/Chicago') as test_66 , from_utc_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC') as test_7 , from_utc_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago') as test_8 , from_utc_timestamp(to_timestamp(EVENTTIME), 'Etc/UTC') as test_77 , from_utc_timestamp(to_timestamp(EVENTTIME), 'America/Chicago') as test_88e.g.

to_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC') as test_3 , to_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago') as test_4
After doing all the test. I just found out that only these two kind of work for both CST and UTC cluster, which give me the correct result if the data comes in as string type timestamp. But I couldn't explain why is that.
For example. if my incoming string data is 2023-11-01 16:10:47 (UTC time) , I will get 2023-11-01T16:10:47Z in UTC cluster and 2023-11-01T11:10:47-05:00 in CST cluster. which are correct using the above two functions, while others are not consistent in both clusters

I also notice another thing. for function to_timestamp(to_timestamp_ntz(STRING_EVENTTIME), 'xxxxx')  no matter what timezone you put ( I tried America/Los_Angeles as well), it will give me correct result in both UTC and CST cluster. BUT, if you just do  to_timestamp(to_timestamp_ntz(EVENTTIME)) without putting anything there, it is not giving correct value.

 

 

0 Kudos