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 INo, 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
Helper IThank 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:
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
BEGINset 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