Converting UTC to local time with daylight saving

[davidwsw]

I have a date column in my database table in UTC and I want to convert it to my local time (Eastern Time), taking into account daily saving.

I tried to do it using the following PowerQuery functions:

• DateTime.AddZone to add the time zone to the UTC time
• DateTimeZone.ToLocal to convert UTC to local
• DateTimeZone.RemoveZone to remove the time zone from the converted time

I also found some posts where people tried to do their own conversion.

https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-sa...

Do the PowerQuery functions not handle the conversion correctly? I'm a bit confused as to why further calculations are needed.

The screenshot below shows the result after using the PowerQuery functions. It looks like the time offset changed from -4 hours to -5 hours at 6AM UTC or 2AM Eastern time.

When I do the conversion using "AT TIME ZONE" in SQL, the result is slightly different.

https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

Here it looks like the offset changed from -4 hours to -5 hours at 2AM UTC.

Which result is correct? The one in SQL or the one from PowerQuery?

[ppm1]

Please see this video for one way to handle DST. Note that it works in desktop but not in the service.

(7) An easy way to handle Daylight Savings Time in Power Query - YouTube

Pat