Forum Discussion
Changing DateTime Value from UTC to AEDT
Your analysis is incorrect.
lt is not a function. Rather it transforms each datetime in your date time column from UTC datetimes to your local timezone time, be it standard time or daylight savings.
You did not provide a usable data sample, so the Source line is there only to create something. I inferred from your question that you had a column of datetimes.
My local timezone is EST (US-East coast).
Please note that the dates are showing as MDY (US style), not DMY.
Our change date from EDT to EST is on the first Sunday in November at 2AM which happens to be Nov 3 2024.
Perhaps it would be more clear if I showed it to you as an additional column
let Source = Table.FromColumns( {List.DateTimes(#datetime(2024,11,1,12,0,0), 24*7,#duration(0,1,0,0))}, type table[DateTimeUTC=datetime]), #"to Local Time" = Table.AddColumn(Source, "DateTimeLocal", each DateTime.From( DateTimeZone.ToLocal( DateTime.AddZone([DateTimeUTC],0))),type datetime) in #"to Local Time"will produce:
Note the change from EDT to EST at 2 AM on Nov 3 in the 2nd column.
A similar result should occur in Australia.
In the example code, I used the Source step to create a column of datetimes. In your code, you should insert the #"to Local Time" step at some point in your code after your datetime column has been created. You can either the version that transforms the column itself, or the one that adds an additional column.
It is only that #"to Local Time" step that is relevant to your question.
The Source step serves only to create a data sample, since you did not provide one.
In the #"to Local Time" step, you may need to change the Source reference and the column name(s) to match whatever you have.
After reviewing the query I understand it is creating the date/time for the local time for 7 days from the start date. This doesn't help my use case as
1. The above query I have based on from modifying queries in previous posts. I need this to be a function or equivalent as In my base tables I have date values for cases that are coming through as UTC that I need to transform to my timezone accounting for daylight savings.
2. If I create the table using your query, it won't relate back to my tables. I could potentially have the column being UTC and then relate the values to the transformed AEST/AEDT time. But I am not sure how this would then impact my report with Filtering Reporting etc
Super UserYour analysis is incorrect.
lt is not a function. Rather it transforms each datetime in your date time column from UTC datetimes to your local timezone time, be it standard time or daylight savings.
You did not provide a usable data sample, so the Source line is there only to create something. I inferred from your question that you had a column of datetimes.
My local timezone is EST (US-East coast).
Please note that the dates are showing as MDY (US style), not DMY.
Our change date from EDT to EST is on the first Sunday in November at 2AM which happens to be Nov 3 2024.
Perhaps it would be more clear if I showed it to you as an additional column
let
Source = Table.FromColumns(
{List.DateTimes(#datetime(2024,11,1,12,0,0), 24*7,#duration(0,1,0,0))}, type table[DateTimeUTC=datetime]),
#"to Local Time" = Table.AddColumn(Source,
"DateTimeLocal", each
DateTime.From(
DateTimeZone.ToLocal(
DateTime.AddZone([DateTimeUTC],0))),type datetime)
in
#"to Local Time"
will produce:
Note the change from EDT to EST at 2 AM on Nov 3 in the 2nd column.
A similar result should occur in Australia.
In the example code, I used the Source step to create a column of datetimes. In your code, you should insert the #"to Local Time" step at some point in your code after your datetime column has been created. You can either the version that transforms the column itself, or the one that adds an additional column.
It is only that #"to Local Time" step that is relevant to your question.
The Source step serves only to create a data sample, since you did not provide one.
In the #"to Local Time" step, you may need to change the Source reference and the column name(s) to match whatever you have.