1

I am new to Synapse Data Warehouse.

Currently, I have a timestamp column named last_update_utc as string data type and it is UTC time. I need to add a new column last_update_est with timestamp as datetime2 data type convert from last_update_utc column - The conversion also needs to account for DST. You can check the sample output below:

id | last_update_utc (string)     | last_update_est (datetime2)
1  | 2020-02-28T17:26:47.483698Z  | 2020-02-28T12:26:47.483698Z
2  | 2021-09-03T22:59:19.093Z     | 2021-09-03T18:59:19.093Z

The first row, It is 5 hours of difference without DST and it is 4 hours of difference with DST. The table name is hub.test and initially, there are id and last_update_utc columns.

Is there a simple function or method to achieve the above task in Synapse DW ?

Thanks

CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
Ken Masters
  • 239
  • 2
  • 17

1 Answers1

2

Convert string to datetime2 and apply AT TIME ZONE to convert UTC to the required time zone.

SELECT CONVERT(DATETIME2, '2021-09-03T22:59:19.093Z') AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'as last_update_est

enter image description here

Refer to this document to learn more on AT TIME ZONE implementation.

NiharikaMoola-MT
  • 4,700
  • 1
  • 3
  • 15
  • Thanks, Niharika -- I upvoted -- your code works in an Azure Synapse Analytics SQL script, connected to Lake or Dedicated. Do you have a version of "AT TIME ZONE" that works in a NOTEBOOK, within a `%%sql` cell? (Or even in a `%%pyspark` cell, within `spark.sql`?) I'm getting the error `no viable alternative at input 'cast(NOW() AT`??? (Note that a notebook requires `cast` instead of `convert`, and `now` instead of `getdate`.) Here's my test code: `select cast(NOW() AT TIME ZONE 'Greenwich Standard Time' AT TIME ZONE 'Eastern Standard Time' as DATETIME) AS EASTERN_TIME_NOW` – Doug_Ivison Dec 21 '22 at 23:19
  • P.S. what we found for notebook `%%sql` cells: `from_utc_timestamp(now(),'EST')` – Doug_Ivison Dec 27 '22 at 01:04