Conversion of time zone from UTC to US/Eastern

Majid
New Contributor

‎10-04-2024 12:37 PM

Hi All,

I am new to databricks and i am writing a query to fetch the data from Databricks and encountered with an error. Please see below the query details and the error. Any help would be appreciated. 

Thank You in Advance ! 

Query: 

cast(TO_UTC_TIMESTAMP_TZ(Cast(substr(START_DATE_TIME,1,22), 'UTC')) AT TIME ZONE 'US/Eastern' as DATE) as START_DT_ET (This line of code is at Line 17 and the error is referring to this particular Line's code.)
 
Error: 

[PARSE_SYNTAX_ERROR] Syntax error at or near 'AT'. SQLSTATE: 42601 line 17, pos 111

, cast(TO_UTC_TIMESTAMP_TZ(Cast(substr(CREATION_DATE_TIME,1,22), 'UTC')) AT TIME ZONE 'US/Eastern' as DATE) as START_DT_ET

0 Kudos

szymon_dybczak
Esteemed Contributor III

‎10-04-2024 01:15 PM - edited ‎10-04-2024 01:20 PM

Hi @Majid ,

Spark SQL doesn't support  AT TIME ZONE, that's why you've got this error. To achieve similar result, you can use to_utc_timestamp or from_utc_timestamp function. Those functions support timezone parameter:

https://docs.databricks.com/en/sql/language-manual/functions/to_utc_timestamp.html