Convert Date/Time in UTC to Local Time with Daylight savings

[Anonymous]

Hello,

I'm not sure where is the better place to do this manipulation, but I have a column with the date/time as following:

The time is in UTC and I want to convert this time to Mountain Daylight Time:

-between second Sunday of March and first Sunday of November you go back 6 hrs so UTC - 6

-between first Sunday of November and second Sunday of March you go back 7 hrs so UTC - 7

I found this article online that shows how to account for daylight savings on the refresh date: https://powerpivotpro.com/2019/01/dst-refresh-date-function-power-bi-service/

But I need to transform a column of data to the appropiate time. Is this possible? If it is, is it better to do in Power Query or Desktop of Power BI? Any help would be much appreciated.

Thank you.

[gpoggi · Accepted solution]

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 hours

According 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

[freshwave]

No, I am only using the desktop version right now with this specific mongodb database.  That sucks, though now reading this and other articles, I do think I recall having seen this mentioned before.

I have been googling to try and find a good answer.  The best I was able to do was tweak the response the person posted for the NZD with DST.  I used the guidance of the page https://info.australia.gov.au/about-australia/facts-and-figures/time-zones-and-daylight-saving to determine what days for it to switch the DST periods, which meant changing it from the last Sunday in September, to the first Sunday in October.

I am not 100% the logic in the isSummerTime is correct, as it seems to be looking at the time based upon UTC, though your 2AM would be actually be the UTC equivalent of previous day 3PM or 4PM.

Please play and test that portion, as it may need to be tweaked.

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

(datetimecolumn as datetime) =>

let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),

isSummerTime =
(date = firstSundayOfOctober and time >= #time(2,0,0))
or
(date > firstSundayOfOctober)
or
(date < firstSundayOfApril)
or
(date = firstSundayOfApril and time >= #time(3,0,0)),

timeZone = 10 + Number.From(isSummerTime),

AST =
DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
AST

[jtao]

Above code seems working fine.  However, some records in the date column are null which is giving me error.  Work fine if it has value in the column. 

Any condition set to solve this ?

[freshwave]

Here is the updated code.  The part trying to convert the isSummerTime to a number was causing an error, so I added another line that checks whether it is null, and and if it is, uses a value of zero.  It worked fine for me.

Please make sure you validate the timing on those DST periods per the code I gave above.  I am concerned still that on those days it changes, there will be a period of 10 or 11 hours that will have the wrong time.

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),

isSummerTime =	
		(date = firstSundayOfOctober and time >= #time(2,0,0)) 
	    or
		(date > firstSundayOfOctober) 
		or
		(date < firstSundayOfApril)  
		or 
		(date = firstSundayOfApril and time >= #time(3,0,0)), 

timeZoneAdj = if datetimecolumn is null then 0 else Number.From(isSummerTime),
timeZone = 10 + timeZoneAdj,
										   
AEST = 
            DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0) 														    
in
    AEST

[MTracy]

I really appreciate you sharing your code with all of us.  This UTC/daylight savings conversion is rough!

In this new segment...

     timeZone = 10 + timeZoneAdj,

 ... is where you have "10" supposed to be where we state our own offset to UTC?  For example, I'm in the US Eastern time (EDT right now), so do I change that to 4?... or 5 and the "isSummerTime" part handles when to change it to 4 during our summer time?  

Thanks in advance!

EDIT:  Figured it out!  Had to use timeZone = (-5 + timeZoneAdj), and that worked.  Verified it's properly converting my historical date column either 4 or 5 hours, depending on whether we're in Daylight Savings Time or not for each date in the column.  Works like a charm, thanks!

---

[freshwave]

Sure, it is not actually my code, I only tweaked it from a post above.

What is your datasource for your data?  Honestly, if anyone is using SQL Server 2016 or greater, changing the line in your SQL I feel is much more efficient.

Something like:

[startDate] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS [startDate]

This can be done for other timezones also, so please refer to https://bertwagner.com/posts/at-time-zone-the-easy-way-to-deal-with-time-zones-and-daylight-savings-....

Okay, so I looked over the code, and I think I have made the tweaks necessary for jtao for the correct DST for AST:

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// The offset from UTC to your Timezone
// ex. AEST is 11 hours later than UTC, so it would be +11
// ex. EST is 5 hours earlier than UTC, so it would be -5
UTCOffset = 11,

firstSundayOfOctober = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday)) + #duration(0, -UTCOffset+1, 0, 0),
firstSundayOfApril = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday)) + #duration(0, -UTCOffset+2, 0, 0),

isDaylightSavingsTime =	
		datetimecolumn >= firstSundayOfApril
		and
		datetimecolumn < firstSundayOfOctober,

AdjustForDST = if datetimecolumn is null then 0 else Number.From(isDaylightSavingsTime),
timeZone = UTCOffset + AdjustForDST,

AEST = 
        DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
    AEST

I am also in EST, and I tweaked and tested the logic which works.  Be aware that we changed DST on the second Sunday of March, and the first Sunday of November.

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),

// The offset from UTC to your Timezone
// ex. AEST is 11 hours later than UTC, so it would be +11
// ex. EST is 5 hours earlier than UTC, so it would be -5
UTCOffset = -5,

firstSundayOfNovember = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday)) + #duration(0, -UTCOffset+1, 0, 0),
secondSundayOfMarch = DateTime.From(Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday)) + #duration(0, -UTCOffset+2, 0, 0),

isDaylightSavingsTime =	
                datetimecolumn >= secondSundayOfMarch
                and
                datetimecolumn < firstSundayOfNovember,

AdjustForDST = if datetimecolumn is null then 0 else Number.From(isDaylightSavingsTime),
timeZone = UTCOffset + AdjustForDST,

EST = 
        DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0)
in
    EST

I realize my test script above was bad, and I below I have pasted a new one that correctly shows the dates with 1 hour increments which will correctly allow you to see if things are changing as expected.

First make sure you add the Local_To_UTC function

let
    Source = (datetimecolumn as nullable datetime) =>

    let

        DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
        DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
        DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),

        UTC_To_Local = DateTimeRemoveZone

    in
        UTC_To_Local
in
    Source

Since they are different functions and date periods for AST and EST, you will need to use the corresponding test script.

For testing the AST, use:

let
    StartDate = #date(2019, 1, 1),
    EndDate = #date(2021, 12, 31),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source =  List.DateTimes(DateTime.From(StartDate),DayCount*24,#duration(0,1,0,0)), 
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(ChangedType,{{"Column1", "DateUTC"}}), 
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "DayOfWeekName", each Date.DayOfWeekName( [DateUTC] )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FilterDates", each if ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 3, 31 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 4, 8 ))  or
                                                                                ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 9, 30 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 10, 8 )) then "Y" else "N"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([FilterDates] = "Y") and ([DayOfWeekName] = "Saturday" or [DayOfWeekName] = "Sunday")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "UTC_to_Local", each UTC_to_Local([DateUTC]), type datetime)
in
    #"Invoked Custom Function1"

For testing the EST, use the same script as above, though change the section from:

#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime),

to be:

#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "EST", each EST([DateUTC]), type datetime),

Okay, need to get back to work.  I have projects with deadlines, and I got carried away working on this.  Hope this is helpful to you both, and perhaps others.

[jtao]

You are champion.  Thanks for your great help !  I will test thoroughly.  Wish I have time to learn M language.

[freshwave]

Very glad to help.  I am better at tweaking others code, than to create myself.  Please use the sample code that I provided above to analyze the periods, as it focuses on the dates around DST only.  That will make it easier to discover issues with the timing, if there is any.

[jtao]

Thank you.  I will test it out.
Every year, the first Sunday of those months are different dates.  Does it mean I need to update this M language every year for firstSundayOfOctober and first SundayOfApril ?

[freshwave]

No, it is using the year of the date you provided to determine the first Sunday of April and October.  My concern still is that on the first Sunday of April that it may not be doing the shift at the exact hour for DST.  That would mean that only that specific period of 10 or 11 hours would be effected by this.

You can see data by calling the function with the below code.  You can control the date range you want by modifying the StartDate and EndDate.

This will let you look at the specific days that will be effected for the time period you are looking at, and also research whether the times are shifting at the right time or not.  I think the IsSummerTime formula may need to be tweaked as I believe it is using the UTC time to determine the DST, not the AST time.

Let me know if you need help with that part.  No matter what, I think this has you a lot closer except for those 11 hours or so between the UTC and AST times of DST.

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

let
StartDate = #date(2019, 1, 1),
EndDate = #date(2021, 12, 31),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount*24,#duration(0,1,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(ChangedType,{{"Column1", "DateUTC"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "DayOfWeekName", each Date.DayOfWeekName( [DateUTC] )),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "FilterDates", each if ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 3, 31 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 4, 8 )) or
( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 9, 30 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 10, 8 )) then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([FilterDates] = "Y") and ([DayOfWeekName] = "Saturday" or [DayOfWeekName] = "Sunday")),
#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime)
in
#"Invoked Custom Function"

[freshwave]

I don't know what your datasource is, though if you are using SQL Server 2016 or greater, you can just do this on your query side, which might be even better.

You can read about it at:

https://bertwagner.com/posts/at-time-zone-the-easy-way-to-deal-with-time-zones-and-daylight-savings-...

SELECT getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time' AS LocalTime

If you were to replace your datetime value for getdate(), it will actually convert it to the AUS Eastern Standard Time.   You could probably quickly create a server function that you wrap around the datetime column which would do the above which would be a lot cleaner looking thatn the large amount of text.

I am not 100% this is the correct logic to create the SQL Server function, though it would be something like the below.  I am surprised someone hasn't created a generic function that you pass the time zone you want from UTC.  I will leave that to greater minds.

CREATE FUNCTION [dbo].[UTCtoAST]
(
@dt_utc datetime2(7)
)
RETURNS datetime
AS
BEGIN

set @dt_utc = @dt_utc AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time'

RETURN @dt_utc

END

[willpage]

If you have a SQL server you can easily create a view with converted times like so:

Right click on a table and go Select Top 1000 rows. This generates a script that has all the columns written out (to save you doing it). In the generated script, delete TOP (1000), so the script reads like SELECT [id], [Column1], [Column1], etc

Now, find your date column (let's say it's called DateTimeColumn in this table) in the list and replace it like this:

CONVERT(DATETIME2, [DateTimeColumn] at time zone 'UTC' at time zone 'New Zealand Standard Time') AS [DateTimeColumnNZST]

Now at the very top of the script editor, add a new line and type CREATE VIEW [MyTable NZST] AS

Click execute. You now have a view you can connect to in Power BI that has a converted time zone. This won't affect the original table at all.

See here for a list of time zones as they should be typed in SQL: https://dzone.com/articles/dates-and-times-in-sql-server-at-time-zone

[Anonymous]

@gpoggi  thank you for the code, I understand the logic behind it and it should work. I am not sure how to create a function with the code and how to enable it to modify the column I want. Could you please provide step-by-step instructions? I am still new to the Power Query Editor. 

[gpoggi · Accepted solution]

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

[vivekravi]

This worked perfect ! Thank you

[deannag]

This is a really helpful post but I'm confused when I'm trying to implement it into my report. When you say,
= (datetimecolumn as datetime) =>
should datetimecolumn be an actual column that exists in the report? I have to convert sale date from UTC to EST in 3 different queries, and the updated version of my Power Bi looks different than the one you've attached. Here's what mine looks like:

And also for anyone that needs to translate this for EST, I used the same query above but with the hours changed:

(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 = (5 - Number.From(isSummerTime))*-1,

EST = 

            DateTime.From(date) 

            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  

            + #duration(0, timeZone, 0, 0)

in

    EST

[tmarton]

The name of the column is not relevant, what is important that the type is date/time. It is important to note that it must not have timezone included. On Power BI Desktop, open your file, go to Transform. Add column>>Invoke custom function and select the function UTC to EST. In the datetimecolumn field, make sure the icon to the left is set to column, then in the field select the name of the date/time column in your data you want to be displayed as EST or any other timezone you have configured it to. If you have multiple columns to be converted you need to repeat these steps for each one. 

[tmarton]

PS when I did the first one, I was on an older version of PBI desktop, that is why it is different.

[SuperBIstar]

Hey Gian Carlo, I was able to use your code to get some dst conversions done.  Thank you. I further modifiied it to use datetimezone. Just change the number in the time zone setting to corispond with your winter offset.

(var_datetime as datetimezone) =>


let

date = DateTime.Date(var_datetime),
time = DateTime.Time(var_datetime),



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 = (5 - Number.From(isSummerTime))*-1,

EST = 
DateTimeZone.SwitchZone(var_datetime,timeZone)



in
    EST

(var_datetime as datetimezone) =>

let

date = DateTime.Date(var_datetime),
time = DateTime.Time(var_datetime),

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 = (5 - Number.From(isSummerTime))*-1,

EST =
DateTimeZone.SwitchZone(var_datetime,timeZone)

in
EST

[Anonymous]

Thank you, Gian Carlo. This works exactly as I wanted it.

[Technowolf]

Hi @gpoggi 

I have tried this solution and tweaked to my requirment.

I am try to convert UTC to CET with Day light Savings

Day Light Saving Start from Last Sunday of March 2:00 AM and Ends in Last Sunday of October 3:00AM 

if Day light Savings its UTC + 2 Hrs and non Daylight Savings is UTC + 1 Hrs

Can you let me know where I am going wrong.  If you want my sample data I will post it.

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
LastSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 28), Day.Sunday),
LastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 28), Day.Sunday),

isDayLightSaving = (date = LastSundayOfMarch and time >= #time(2,0,0))
or
(date > LastSundayOfMarch and date < LastSundayOfNovember)
or
(date = LastSundayOfNovember and time >= #time(3,0,0)),

timeZone = (-2 - Number.From(isDayLightSaving))*-1,

CET =
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)

in
CET

Regards,

Charles Thangaraj