Daylight Saving Time And Time Zones In M

Quite a few people (Ken Puls, Reza Rad and today Chris Koester) have blogged about how the M date/time zone functions work in Power Query/Power BI, and the related problem of turning a UTC time into the actual time in any given location taking Daylight Saving Time changes into account. I don’t have much to add to what they have written, but I did learn something new about this subject from a customer last week: it is possible to do the conversion in pure M if the time zone you’re converting to is your PC’s own local time zone.

Here’s an example. In 2017 the clocks in the UK went forward at 1am on Sunday March 26th. Given the following table with two times in it, one of which is 1pm on Saturday March 25th and one of which is 1pm on Sunday March 26th:

…if you assume that these times are both UTC, here’s a query that returns the correct equivalent local time for me (because my PC is set to UK time) for both of them:

```let
Source =
Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//Change the type of the column to datetime
ChangeType1 =
Table.TransformColumnTypes(
Source,
{{"Time", type datetime}}),
//Turn this into a UTC time
ConvertToUTC =
ChangeType1,
"UTC",
//Change the data type to datetimezone
ChangeType2 =
Table.TransformColumnTypes(
ConvertToUTC,
{{"UTC", type datetimezone}}),
//Convert to local PC time
ConvertToLocal =
ChangeType2,
"Local",
each DateTimeZone.ToLocal([UTC])),
//Change type to datetimezone
ChangeType3 =
Table.TransformColumnTypes(
ConvertToLocal ,
{{"Local", type datetimezone}})
in
ChangeType3
```

Here’s a brief explanation of what the query does:

• First it reads the times from the Excel table and sets the Time column to be datetime data type
• It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
• Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function

As you can see from the output table, the DateTimeZone.ToLocal() function has correctly handled the UK Daylight Saving Time change by converting the UTC time 13:00 on March 25th 2017 to 13:00 UK time, and converting the UTC time 13:00 on March 26th 2017 to 14:00 UK time.

Frustratingly there doesn’t seem to be a way to convert a UTC time to the correct local time in any given time zone in M – the DateTimeZone.SwitchZone() function just adds/removes hours without taking Daylight Saving Time into account, so it’s not really very useful. Apart from all the blog posts linked to at the start of this post, when I posted this question on the Power Query forum I got a very helpful answer from Marcel Beug with a completely different approach to solving the problem here, although it’s still not a straightforward one. If anyone from Microsoft is reading, it would be great to have a new M function to handle this requirement!

15 responses

1. Curt Hagenlocher says:

It’s on our backlog.

2. Dylan Morgan says:

Great post Chris! When using this with Power BI, would it evaluate once at the Gateway, at the Power BI server, or would it evaluate at the client (Assuming these were all at different Time zones?

• If you deploy to the server and refresh your data, it’s going to get the server time which is UCT.

3. Hi Chris,
had you any update on this?

• There haven’t been any changes as far as I know

4. Gordon Parmenter says:

Hi Chris,
I noticed it is a year from the last comment on this subject. I have been trying to resolve how best to handle Daylight Saving Hours but without any luck so far. My reports need to work on “service” and therefore when localtime is same as UTC.

I have managed to add a daylight savings indicator on the DAX built CalendarAuto() Dates table but really need this at the power query stage to enable me to build a model relationship between Dates table and the imported data(adjusted date).not the UCT date.

With UCT date/time of 31 March 2020 23:00, actually adjusting to 1 April 2020 00:00 hours it makes quite a difference. i.e dates not only wrong day but wrong month.

• Nothing has changed since my last comment, unfortunately…

• Gordon Parmenter says:

I have managed to come up with the following simply query to handle BST (Daylight Saving) in the UK.

This takes the date as a parameter, needs to be in Date/Time format, and checks to see if the date is between the last Sunday in March and last Sunday in October (UK Daylight Saving) for the year of the date provided. If it does then it adds 1 hour to the date and returns it to be used. I got the basis for this from other similar US versions I found.

let
AdjustedDate = (InDate as any) =>
let
OutDate = if InDate >= Date.StartOfWeek(#datetime(Date.Year(InDate),03,31,0,0,0),1) and
InDate <= Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1)
then InDate + #duration(0,1,0,0)
else InDate
in
OutDate
in

5. Damian says:

I’ve just come across this problem in 2022 – there still doesn’t seem to be a built-in solution.
And I then found that Gordon Parmenter’s solution, while clever, is slightly incorrect and goes from Monday to Monday and not the Last Sunday of March to the last Sunday of October, and ignores the times the clocks officially change (1am GMT forward, 2am BST backward). So I came up with this which I’m pretty certain is correct:
let
BSTAdjustedDateTime = (_myDate as datetime) =>
let
_BSTCheckDate =
if _myDate >= Date.StartOfWeek(#datetime(Date.Year(_myDate),03,31,0,0,0),Day.Sunday) + #duration(0,1,0,0) and
_myDate < Date.StartOfWeek(#datetime(Date.Year(_myDate),10,31,0,0,0),Day.Sunday) + #duration(0,1,0,0)
then _myDate + #duration(0,1,0,0)
else _myDate
in
_BSTCheckDate
in

6. Gordon says:

Nice improvement on taking account of official change in the clocks. I Didn’t need that precision on my original solution as I had a date rather than time issue. i.e Midnight coming up as 23:00 the day before.

My solution was built in my first few months of Power Query/BI endeavours, and after some frustration at not finding anyone had done this before. Now almost 2 years on I have learned and forgotten a lot along the way but did fix the Monday issue once we got to October and I realised the error. Now use Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),0) rather than Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1).

7. Great stuff. The blogpost was a while ago, and I’m sure you have a solution for it. For others interested, the code I like using is:

```let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date         = Date.From(UTC_DateTimeZone),
StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
CET_Timezone
```

If you like you can find an elaboration on my blog: https://gorilla.bi/power-query/last-refresh-datetime/

Enjoy!

• Unfortunately this isn’t a 100% reliable solution: you have hard coded the dates when daylight savings starts and ends (it can vary from year to year and country to country, and some countries don’t always have daylight savings time), and it doesn’t account for historic variations in offsets.

This site uses Akismet to reduce spam. Learn how your comment data is processed.