Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago
Solved

UTC to AEST

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 solution...
Need Help
Show and Tell
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

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

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

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's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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:

     

    • ElliotP's avatar
      ElliotP
      Post Prodigy
      9 years ago

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

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

         

    • tombradley's avatar
      tombradley
      New Member
      9 years ago

      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's avatar
        ElliotP
        Post Prodigy
        9 years ago

        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.