cancel
Showing results for 
Search instead for 
Did you mean: 
Get Started Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

TIMEZONE

nan
New Contributor II

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.

 

 

1 REPLY 1

Kaniz
Community Manager
Community Manager

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

  1. Timestamp Type in Databricks:

  2. Literals for Timestamps:

  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.