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
Helper II 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:
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
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