Forum Discussion
Convert Date/Time in UTC to Local Time with Daylight savings
Hi Anonymous ,
I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:
(datetimecolumn as datetime) => let date = DateTime.Date(datetimecolumn), time = DateTime.Time(datetimecolumn), firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday), SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday), isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0)) or (date > SecondSundayOfMarch and date < firstSundayOfNovember) or (date = firstSundayOfNovember and time >= #time(1,0,0)), timeZone = (7 - Number.From(isSummerTime))*-1, MDT = DateTime.From(date) + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) + #duration(0, timeZone, 0, 0) in MDT
So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hoursAccording to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime
If you have any question or if you find any error on the code, just let me know.
Regards,
Gian Carlo Poggi
Sure Anonymous ,
Right click on Queries pane and add a new Blank Query:
Then right click on this new query and select Advanced Editor:
In this new window erase all, paste the my code and click DONE:
Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":
Then in order to use this function in your table you have different options, one option is going to your query or table, then click on Add Column / Invoke Custom Function, then put a name to this new column, select your function (in my case UTC_to_MDT) and select the column from your table you need to apply this function to (in my case "Date"):
And then you will see the new date added :
Hope this helps.
Regards,
Gian Carlo Poggi
AdministratorThanks for sharing this solution, it's pretty disappointing that PowerQuery doesn't have proper time zone support.
I've tweaked it for conversion from UTC to UK time (aka Europe/London in tzdata)
From When do the clocks change? - GOV.UK (www.gov.uk):
In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.
The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).
When the clocks go back, the UK is on Greenwich Mean Time (GMT).
datetime version:
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.
// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
isSummerTime =
(date = ForwardDate and time >= #time(1,0,0))
or
(date > ForwardDate and date < BackDate)
or
(date = BackDate and time < #time(1,0,0)),
timeZone = Number.From(isSummerTime),
Europe_London = datetimecolumn + #duration(0, timeZone, 0, 0)
in
Europe_London
datetimezone version:
let
Source = (datetimecolumn as datetimezone) =>
let
// This version ignores the zone information in the input, but adds it to the output
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
// From https://www.gov.uk/when-do-the-clocks-change
// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,
// and back 1 hour at 2am on the last Sunday in October.
// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
isSummerTime =
(date = ForwardDate and time >= #time(1,0,0))
or
(date > ForwardDate and date < BackDate)
or
(date = BackDate and time < #time(1,0,0)),
timeZone = Number.From(isSummerTime),
Europe_London = DateTime.AddZone(DateTimeZone.RemoveZone(datetimecolumn) + #duration(0, timeZone, 0, 0) , timeZone)
in
Europe_London
in
Source
I hope that's useful to someone!
Hi Syndicate_Admin,
You write "...and back 1 hour at 2am on the last Sunday in October", then there should be time < #time(2,0,0)) instead of time < #time(1,0,0)).
(I had a similar error, I fixed it in my "UTC to CET" post.)
- Syndicate_Admin
No, because I'm converting from UTC to UK time. The "2 am" is in British Summer time, which is 01:00 UTC.
The #time(2,0,0) function is time zone independent. When the current time (UTC, CET...) reaches 2 am, it switches to winter time.
- Syndicate_Admin
When the clocks go back, each minute looks like this in UTC and local time:
- 00:58 UTC / 01:58 BST
- 00:59 UTC / 01:59 BST
- 01:00 UTC / 01:00 GMT
- 01:01 UTC / 01:01 GMT
The government says "Clocks go back at 2am" because your clock is in BST right up until that moment, at which point it should become GMT.
The source data is in UTC, so you need to figure out what the switch time is in UTC, which will probably not be the same as the switch time in local time (might even be on a different day for places like Australia!)