UTC to AEST

[ElliotP]

Morning,

The DateTime column in my sql table is in UTC time, but I would like this column to be in the AEST (-10 or -11) timezone. I've read quite a lot through the forums and all of the solutions don't seem to handle Daylight savings well; they simply either offset or +/- duration (less effective offset).

I was wondering if anyone had a way to convert UTC to AEST reliably taking into account daylight savings or if this is the kind of thing best handled by a stored procedure in the sql table while using data factory?

Very useful post: https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m-p/45533#...

[steven_steven]

Perhaps this can help others.

I've taken a different way to solve for DST with the plan to not rely on a table (not that there's anything wrong with that either!)

Use case may be limited as it addresses converting UTC to another timezone with daylight savings time support with DST starting on the 2nd Sunday in March and ending on the 1st Sunday in November.

Last Refresh Pacific =

  // First let's find the date of the second Sunday of March
  // For the forumla below:
  // In DAX, we use the functions DATE() and WEEKDAY()
  // "1+7*2", generically, "1+7*n" where n represents 
  // the nth occurence of that "weekday name" 
  // "8-1", generically, "8-y", where 
  // y can be a number from 1 to 7, where
  // 1 = Sunday through to 7 = Saturday
  //
  // Thus, the date of the second Sunday of the month
  // is returned to the variable ZZQQ_DST_START
  // 
  // ZZQQ_DATE is the date/time in UTC.

  VAR ZZQQ_DST_START = DATE(YEAR(ZZQQ_DATE),3,1+7*2) 
    - WEEKDAY(DATE(YEAR(ZZQQ_DATE),3,8-1))

  // Next let's find the date of the first Sunday in November 
  VAR ZZQQ_DST_END = DATE(YEAR(ZZQQ_DATE),11,1+7*1) 
    - WEEKDAY(DATE(YEAR(ZZQQ_DATE),11,8-1))

  RETURN 

  IF (
    // Evaluate if ZZQQ_DATE is between DST start and end
    // If yes, change time to Pacific Daylight Savings Time
    // else, change time to Pacific Standard Time
    (ZZQQ_DATE >= ZZQQ_DST_START) && (ZZQQ_DATE <= ZZQQ_DST_END), 
    ZZQQ_DATE + (-7/24), // If condition matches, convert UTC to PDT
    ZZQQ_DATE + (-8/24)  // Else convert UTC to PST
  )

[RachnaV]

@steven_steven 

This worked for me, I was looking for a way to address timezone issue in DAX itself without creating a calculated column or using Power Query languaue. I changed this a bit to cater to my requirements and its working fine.

Would you pleas explain the logic in below two lines wherein you are subtracting weekday from a date, also where can i find the syntax of date function that explains how you have used the day part of date function (i.e. 1+7*2 and 8-1)  :

  VAR ZZQQ_DST_START = DATE(YEAR(ZZQQ_DATE),3,1+7*2) 
    - WEEKDAY(DATE(YEAR(ZZQQ_DATE),3,8-1))

  // Next let's find the date of the first Sunday in November 
  VAR ZZQQ_DST_END = DATE(YEAR(ZZQQ_DATE),11,1+7*1) 
    - WEEKDAY(DATE(YEAR(ZZQQ_DATE),11,8-1))

[MarcelBeug]

And what happened with my answer to your same question????

[ElliotP]

Evening,

For some reason I didn't see the post pop up a while ago, I'm so sorry.

I've had a look, I'm not sure how to create that original TimeTable? As well, the pbix will have a locale of United States as so I'm able to convert the string which my datetime is identified and stored as, into a DateTime data type.

[v-caliao-msft]

@ElliotP,

We can switch time zone when add a custom column. I have tested it on my local environment.

   

Regards,

Charlie Liao

[MarcelBeug]

@v-caliao-msft your solution won't work in this case as it doesn't take into account Daylight Saving Time (DST) switches.

[ElliotP]

This is the issue we are working to solve.

I've seen a complicated potential solution on radacad (I can find the link if you haven't seen it before); but it relies upon working with an external API which seems a tad overkill.

[MarcelBeug]

If I strip down my solution in the other post to the bare minimum required for this specific timezone: you need a table with the clock switches and a function for the conversion.

Query UTCtoAET returns a table with UTC-datetime-stamps between 1/1/2010 and 1/1/2030 in witch the clocks are adjusted in Australia, together with the offset after the switch:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJLDsMgEEOvUrGOxHwITXqVKPe/RlsCEhiz9ZOFxo/rCho1mqi85CMStqAa7u0KKfoTa665lFwlGoBWKLmSggJ4Ch69ACONDKA9UXInhR1AK5Q8kUIC0Aol30nBAQxX59XVeS6U/I2FM7oAGGc6VjMdi5nO1Uwnv9qYawfQfw5jrg1AP5Mx1Qqgn8km1XUmI6r/RxtTvQPo/4Yx1QnAsBJT7QCGlSbVbaVJdT16Mt2OnkzXv2FMdQbwa9xf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UTC = _t, UTCOffset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UTC", type datetime}, {"UTCOffset", Int64.Type}})
in
    #"Changed Type"

This was created via "Enter Data". You can make adjustments if you press the gear button right from the step Source in the query editor:

Function fnUTCtoAET converts UTC datetimes to AEDT/AEST:

(DateTimeUTC as datetime) as datetime => DateTimeUTC + #duration(0,Table.Last(Table.SelectRows(UTCtoAET, each [UTC] <= DateTimeUTC))[UTCOffset],0,0)

Example query that converts some UTC datetimes to AEDT/AEST:

let
    Source = Table.FromColumns({List.DateTimes(#datetime(2017,6,1,0,0,0),10,#duration(60,0,0,0))},type table[UTC = datetime]),
    #"Invoked Custom Function" = Table.AddColumn(Source, "AEST/AEDT", each fnUTCtoAET([UTC]), type datetime)
in
    #"Invoked Custom Function"

Result:

[tombradley]

Hi Marcel,

Thanks for this post... but can you explain how to adjust your instructions for different timezones?

So changing this....

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJLDsMgEEOvUrGOxHwITXqVKPe/RlsCEhiz9ZOFxo/rCho1mqi85CMStqAa7u0KKfoTa665lFwlGoBWKLmSggJ4Ch69ACONDKA9UXInhR1AK5Q8kUIC0Aol30nBAQxX59XVeS6U/I2FM7oAGGc6VjMdi5nO1Uwnv9qYawfQfw5jrg1AP5Mx1Qqgn8km1XUmI6r/RxtTvQPo/4Yx1QnAsBJT7QCGlSbVbaVJdT16Mt2OnkzXv2FMdQbwa9xf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UTC = _t, UTCOffset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UTC", type datetime}, {"UTCOffset", Int64.Type}})
in
    #"Changed Type"

to convert 

UTC to GMT/BST... or

UTC to EST/EDT

etc etc

Thanks very much,

Tom

[ElliotP]

It's all about changing the reference table UTCtoAET as that specifies the dates. You have to enter the dates yourself manually, but given the functionality and how this is the only solution anywhere on web, it's worth the 10mins grabbing the dates.

[MarcelBeug]

If you want to convert dates and times between timezones, you must create an Excel file for each of those timezones.

Before each run, you must adjust the timezone on you local computer. And close and reopen the Excel file, if I remember correctly.

FYI it took me about 3 hours for all approx. 130 timezones, so let's say 1-2 minutes per timezone.

Next you need to combine the files in a separate Excel sheet, like this:

This is just a simple combine of the individual files, with the file names as timezones.

(OK, I added semicolons but that's not really necessary).

Finaly you need a function for the actual conversion. This function will first calculate the UTC datetime from the source datetime and next calculate the datetime in the destination zone from the UTC datetime. Both source and destination time zones must be the names of the zones in the combined table.

I can share the code with my file- and tablename.

Unfortunately it is all in Dutch (except for the function name), but it should still work (after you adjust the source to your source).

Note: the code was written back in December 2016 and it may not be very efficient (I'm not sure). At some time in the (near?) future I will create new code in English, probably more efficient by creating a large calendar table for each quarter of an hour, so data can be merged directly.

For now, this is the code I can share:

let
    fnDateTimeBetweenZones = (ZoneFrom as text, DateTimeFrom as datetime, ZoneTo as text) as datetime =>
let
//    ZoneFrom = "(UTC+10:30) Lord Howe Island",
//    DateTimeFrom = DateTime.FixedLocalNow(),
//    ZoneTo = "(UTC-09:00) Alaska",
    Bron = Excel.Workbook(File.Contents("C:\Users\Marcel\OneDrive - Bemint\Office 365\Power Query\DateTimeTables\Windows Time and Dates.xlsx"), null, true),
    GlobalTimeTable_Table = Bron{[Item="GlobalTimeTable",Kind="Table"]}[Data],
    #"Type gewijzigd" = Table.TransformColumnTypes(GlobalTimeTable_Table,{{"Tijdzone", type text}, {"UTC", type datetime}, {"Lokaal", type datetime}}),
    #"Gefilterde rijen" = Table.SelectRows(#"Type gewijzigd", each ([Tijdzone] = ZoneFrom)),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Gefilterde rijen", "DateTimeFrom", each DateTimeFrom),
    #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Aangepaste kolom toegevoegd", "Lokaal<=DateTimeFrom", each [Lokaal]<=[DateTimeFrom]),
    #"Gefilterde rijen1" = Table.SelectRows(#"Aangepaste kolom toegevoegd1", each ([#"Lokaal<=DateTimeFrom"] = true)),
    #"Laatste rijen behouden" = Table.LastN(#"Gefilterde rijen1", 1),
    #"Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Laatste rijen behouden", "UTCDatumTijd", each [DateTimeFrom]+([UTC]-[Lokaal])),

/* Nu de UTC-tijd omrekenen naar lokale tijd in ZoneTo */
    #"Gefilterde rijen2" = Table.SelectRows(#"Type gewijzigd", each ([Tijdzone] = ZoneTo)),
    #"Aangepaste kolom toegevoegd3" = Table.AddColumn(#"Gefilterde rijen2", "UTCDatumTijd", each #"Aangepaste kolom toegevoegd2"[UTCDatumTijd]{0}),
    #"Aangepaste kolom toegevoegd4" = Table.AddColumn(#"Aangepaste kolom toegevoegd3", "UTC<=UTCDatumTijd", each [UTC]<=[UTCDatumTijd]),
    #"Gefilterde rijen3" = Table.SelectRows(#"Aangepaste kolom toegevoegd4", each ([#"UTC<=UTCDatumTijd"] = true)),
    #"Laatste rijen behouden1" = Table.LastN(#"Gefilterde rijen3", 1),
    #"Aangepaste kolom toegevoegd5" = Table.AddColumn(#"Laatste rijen behouden1", "DateTimeTo", each [UTCDatumTijd]+([Lokaal]-[UTC])),
    DateTimeTo = #"Aangepaste kolom toegevoegd5"[DateTimeTo]{0}
in
    DateTimeTo
in
    fnDateTimeBetweenZones

[ElliotP]

This is incredible and definitly should be stickied as this is probably the first publically available solution to this major problem.

So:

1: I create a table using enter data for the past few years back and forward as I need for the days when daylight savings change.

2: Create a function in my data table using the Custom Function and link my date table with my Custom Function generated AEST column?

[MarcelBeug · Accepted solution]

You can use the table and function from my post as illustrated in this video:

[ElliotP]

Would anybody know how to increase the initial UTC convert time dates? They start in 2010 but I was hoping to push them to a little earlier (1990).

The initial conversion looks to be from a created date series

(@MarcelBeugif you're about)

[ElliotP]

This should be stickied. This is truly amazing. Thank you so much @MarcelBeug; I really do appreciate it. You went above and beyond.

This is the best and only solution I've seen to this problem and there seem to be quite a lot of solution types to this problem but none are able to solve the daylight savings issue.

Thank you so much.

[MarcelBeug]

It is always posssible to converrt between local time and UTC time, but then you are dependent on the time zone setting of your computer (which might as well be a server running on UTC).

Example query:

let
    Source = Table.FromColumns({List.DateTimes(#datetime(2017,1,1,1,0,0),10,#duration(25,1,0,0))},type table[UTC = datetime]),
    #"Added Custom" = Table.AddColumn(Source, "Local", each DateTime.From(DateTime.AddZone([UTC],0,0)), type datetime)
in
    #"Added Custom"