Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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: The time is in UTC and I want to convert this time to Mountain Day...
Need Help
gpoggi's avatar
gpoggi
Icon for Responsive Resident rankResponsive Resident
7 years ago

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

SuperBIstar's avatar
SuperBIstar
Frequent Visitor
4 years ago

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