cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bogdans
Helper II
Helper II

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

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:image.png

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.

 

2 ACCEPTED SOLUTIONS
gpoggi
Advocate V
Advocate V

Hi @bogdans ,

 

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

 

 

View solution in original post

Sure @bogdans ,

 

Right click on Queries pane and add a new Blank Query:

 

PQ_1.jpg

 

Then right click on this new query and select Advanced Editor:

 

 

PQ_2.jpg

 

In this new window erase all, paste the my code and click DONE:

 

 

PQ_3.jpg

 

Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":

 

 

PQ_4.jpg

 

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"):

 

 

PQ_6.jpg

 

And then you will see the new date added :

 

 

PQ_7.jpg

 

Hope this helps.

 

Regards,

 

Gian Carlo Poggi

View solution in original post

9 REPLIES 9
dax
Community Support
Community Support

Hi bogdans,

I will test this for you and inform you as soon as I get it . And you also could refer to this link for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

gpoggi
Advocate V
Advocate V

Hi @bogdans ,

 

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

 

 

View solution in original post

tmarton
Frequent Visitor

Thanks gpoggi!! this worked perfectly to resolve my time issue when power BI service refreshed my data!!

Thank you so much @gpoggi  This works perfectly and it's also improved my understanding of custom functions in Power Query. My local time zone is NZST and being in the southern hemisphere our daylight savings spans the new year period, so I have to change the formula in isSummerTime slightly.

(datetimecolumn as datetime) =>

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

lastSundayOfSeptember = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),

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

timeZone = 12 + Number.From(isSummerTime), 
										   
NZST = 
            DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0) 														    
in
    NZST

 

Hey thanks for posting that @willpage , I used your mod to get Australian Eastern time and it works a treat.

@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. 

Sure @bogdans ,

 

Right click on Queries pane and add a new Blank Query:

 

PQ_1.jpg

 

Then right click on this new query and select Advanced Editor:

 

 

PQ_2.jpg

 

In this new window erase all, paste the my code and click DONE:

 

 

PQ_3.jpg

 

Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":

 

 

PQ_4.jpg

 

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"):

 

 

PQ_6.jpg

 

And then you will see the new date added :

 

 

PQ_7.jpg

 

Hope this helps.

 

Regards,

 

Gian Carlo Poggi

View solution in original post

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors