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
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.
Responsive ResidentSure 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
Thank you, Gian Carlo. This works exactly as I wanted it.
TechnowolfHelper II
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
CETRegards,
Charles Thangaraj
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) =>
letdate = 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
deannagHelper I
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) =>letdate = 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)inEST
tmartonThe 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.
This worked perfect ! Thank you
