TIMEZONE

[nan]

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.

[Kaniz_Fatma]

Hi @nan,  Let’s dive into the intricacies of timestamp interpretation in Databricks on AWS.

1. Timestamp Type in Databricks:

   • A timestamp in Databricks represents an absolute point in time, comprising values for year, month, day, hour, minute, and second, with the session’s local time zone.
   • The timestamp type is internally normalized and persisted in UTC.
   • When extracting values from a timestamp, the local session time zone is applied.
   • The supported range of timestamps is from June 23, -5877641 CE to July 11, +5881580 CE¹.

2. Literals for Timestamps:

   • You can represent timestamps using various formats:
     • yyyy[-mm[-dd]][T]hh[:mm[:ss[.sss[.sss][.sss][.sss][zoneId]]]]
     • [+|-]yyyy[...]-[m]m-[d]d[T][h]h[:][m]m[:][s]s[.][ms][ms][ms][us][us][us][zoneId]
   • Here are some examples:
     • TIMESTAMP'0000': January 1, 0000, at midnight.
     • TIMESTAMP'2020-12-31': December 31, 2020, at midnight.
     • TIMESTAMP'2021-7-1T8:43:28.123456': July 1, 2021, at 8:43:28.123456.
     • TIMESTAMP'2021-7-1T8:43:28UTC+3': Converted to America/Los_Angeles time zone: June 30, 2021, at 22:4...¹.

3. Your Specific Scenario:

   • The two functions that work consistently for both CST and UTC clusters are:
     • to_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC')
     • to_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago')
   • These functions correctly interpret the incoming string data as UTC and CST, respectively.
   • Other functions may not yield consistent results due to differences in time zone handling.
   • The reason behind this behavior lies in how these specific functions handle time zones and conversions.

4. Explanation:

   • The first function (to_timestamp(to_timestamp_ntz(EVENTTIME), 'Etc/UTC')) converts the incoming string to UTC.
   • The second function (to_timestamp(to_timestamp_ntz(EVENTTIME), 'America/Chicago')) converts the incoming string to CST.
   • These functions explicitly specify the target time zone, ensuring consistent results across clusters.

Remember that timestamps with local time zones are internally normalized to UTC, and the session’s local time zone is applied during extraction. If you encounter further inconsistencies, consider using these two functions for reliable results.