How to convert timestamp taking into account daylight savings?

Question

How to convert timestamp taking into account daylight savings?

DC-q489h2 126

I have a timestamp in my data that comes in UTC and I would like to convert to EST/EDT taking into account daylight savings time. Can this be done as part of a data flow?

Henk Standhardt 1 Reputation point

2021-12-01T15:52:29.36+00:00

Have a look at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
EDT is there in the form of EST5EDT. But the comment says: "Choose a zone that observes EST with United States daylight saving time rules, such as America/New_York."
Out of our Microsoft/Windows/Azure comfort zone. I had the same issue with CET, started using Europe/Amsterdam.
The time zone names of this tz database are used by Data Flows, hinted in this documentation (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#fromutc), which should also contain a reference to the tz database.

1 answer

Your answer

Henk Standhardt 1 Reputation point

2021-12-01T15:52:29.36+00:00

Have a look at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
EDT is there in the form of EST5EDT. But the comment says: "Choose a zone that observes EST with United States daylight saving time rules, such as America/New_York."
Out of our Microsoft/Windows/Azure comfort zone. I had the same issue with CET, started using Europe/Amsterdam.
The time zone names of this tz database are used by Data Flows, hinted in this documentation (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#fromutc), which should also contain a reference to the tz database.

Answer 1

svijay-MSFT 5,256 Microsoft Employee Moderator

Hello @DC-q489h2 ,

Thanks for the question and using MS Q&A platform.

You could make use of the fromUTC() function.

Converts to the timestamp from UTC to TimeZone that you pass. This handles the DST as well.

So, if you would like to convert to EST.

fromUTC(<INPUT TIMESTAMP>,'EST')

I just tried converting current utc timestamp to EST

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

DC-q489h2 126 Reputation points

2021-11-11T14:15:52.2+00:00

HI @svijay-MSFT Thank for you your feedback. I should have clarified that I need to dynamically take into account daylight savings time. From my experience with this function it does not take into account daylight savings time, rather you would have to switch between using 'EST' and 'EDT' depending on time of the year.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-15T16:30:04.853+00:00

Hello @DC-q489h2 ,

From my testing - the below was the output when I changed from UTC to EST.

3:30 PM UTC 11/15/2021 was converted to 10:30 AM EST 11/15/2021. My understanding is that, this is taking the daylight saving into picture ?

Are you trying to convert the past dates ? if this is the case, you could do an if condition - manually subtract hours - using addHours functions. ( reference for addhours : https://www.youtube.com/watch?v=BhVa4CxRf7s)

Note : I tried EDT - but I dont think this a supported Timezon in the ADF. Please correct me if this is not case from your testing.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-18T10:53:18.787+00:00

Hello @DC-q489h2 ,

Just checking in if you have had a chance to see the previous response. If my understanding is incorrect - can you please provide an instance/ example to help my understanding ?
DC-q489h2 126 Reputation points

2021-11-18T14:10:25.143+00:00

Hi @svijay-MSFT I have not had a chance to test this yet, but will update you once I do. Thank you!
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-25T07:47:19.6+00:00

Hello @DC-q489h2 ,
Following up to see if your issue was resolved. And, if you have any further query do let us know.
TM 0 Reputation points

2023-03-13T18:32:13.6833333+00:00

I am actually using fromUTC function to convert, however it does not consider DST time. I am converting to fromUTC(timestamp_column,'MST'), however it is always converting things with -7 hours not considering DST
Doug Ivison 21 Reputation points

2023-06-28T19:14:52.6766667+00:00

This covers how to imply daylight savings in an ASA notebook (for example dealing with Lake tables), but not how to imply daylight savings with dedicatedp1 / the T-SQL used for sql scripts and pipeline sql tasks.

Share via

How to convert timestamp taking into account daylight savings?

1 answer

Your answer