none
Finding local time in different timezones

    Question

  • Hi all,

    I have a table containing sales data where the date and time of the transaction is stored in UTC, but I want to analyse this data by the actual date and time at the place of sale. If all the sales transactions take place in the UK then I can use the DateTimeZone.ToLocal() M function to convert the UTC value to the correct local time for my PC (which is also set up to use the UK time zone) – and, crucially, it handles Daylight Saving Time. However I have still have problems:

    • What happens if my M query is run on a different machine in a different time zone? I want to guarantee that my conversion is always to UK time, not the time zone of the machine the query is running on
    • Similarly, what happens if I want to run a query on my PC (in the UK time zone) and get correct local times for, say, PST?

    I don’t think this is possible at the moment, because M functions like DateTimeZone.SwitchZone() just had/remove a time offset from a DateTimeZone value and don’t handle things like Daylight Saving Time in different countries. Or am I missing something?

    Thanks,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, March 22, 2017 9:09 AM

Answers

  • Hi Chris,

    Well, it can be done, with some time investment.

    The code below gives you a list with all UTC and local time stamps in the period 1/1/2000 through 12/31/2027 on which the clock changes.

    Just run this query in an Excel file, save the file with the name of the current timezone, change the timezone on your computer, go out and back in Excel again, open the file with the query, run the query again, save the file with the name of the timezone etcetera, etcetera for all timezones (you require).

    Now you can combine the results and you have 1 big lookup file for all (your) timezones, that allows converting local times in each zone to the local time in another zone.

    All you have to do now is keep an eye on the timezone updates from Microsoft and refresh your files when appropriate. Coincidentally, an update just came in yesterday.

    I created the code with the Dutch version of Power BI Desktop some time ago when I was not yet very experienced with Power Query.

    let
        Bron = {36527..46753},
        UTC1Nummers = Table.FromList(Bron, Splitter.SplitByNothing(), {"UTC1"}, null, ExtraValues.Error),
        UTC2Nummers = Table.AddColumn(UTC1Nummers, "UTC2", each [UTC1] - 1),
        NummersNaarDatumTijd = Table.TransformColumnTypes(UTC2Nummers,{{"UTC1", type datetime}, {"UTC2", type datetime}}),
        UTC1MetZone = Table.AddColumn(NummersNaarDatumTijd, "UTC1+Zone", each DateTime.AddZone([UTC1],0,0)),
        UTC2MetZone = Table.AddColumn(UTC1MetZone, "UTC2+Zone", each DateTime.AddZone([UTC2],0,0)),
        Lokaal1 = Table.AddColumn(UTC2MetZone, "Lokaal1", each DateTime.From([#"UTC1+Zone"])),
        Lokaal2 = Table.AddColumn(Lokaal1, "Lokaal2", each DateTime.From([#"UTC2+Zone"])),
        Bewaren = Table.AddColumn(Lokaal2, "Bewaren", each if [UTC1]=#datetime(2000,1,2,0,0,0) then true else ([Lokaal1]-[UTC1])<>([Lokaal2]-[UTC2])),
        KolommenVerwijderd1 = Table.RemoveColumns(Bewaren,{"UTC2", "UTC1+Zone", "UTC2+Zone", "Lokaal1", "Lokaal2"}),
        GefilterdeDatums = Table.SelectRows(KolommenVerwijderd1, each ([Bewaren] = true)),
        PerKwartier = Table.AddColumn(GefilterdeDatums, "UTC", each {(Int64.From([UTC1])-1)*96..Int64.From([UTC1])*96}),
        KolommenVerwijderd2 = Table.RemoveColumns(PerKwartier,{"UTC1", "Bewaren"}),
        PerKwartierUitgevouwen = Table.ExpandListColumn(KolommenVerwijderd2, "UTC"),
        GedeeldDoor96 = Table.TransformColumns(PerKwartierUitgevouwen, {{"UTC", each _ / 96, type number}}),
        UTCMin1Kwartier = Table.AddColumn(GedeeldDoor96, "UTCMin1Kwartier", each [UTC] - 1 / 96, type number),
        KwartierenNaarDatumTijd = Table.TransformColumnTypes(UTCMin1Kwartier,{{"UTC", type datetime}, {"UTCMin1Kwartier", type datetime}}),
        UTCMetZone = Table.AddColumn(KwartierenNaarDatumTijd, "UTCMetZone", each DateTime.AddZone([UTC],0,0)),
        Min1KwartierMetZone = Table.AddColumn(UTCMetZone, "Min1KwartierMetZone", each DateTime.AddZone([UTCMin1Kwartier],0,0)),
        Lokaal = Table.AddColumn(Min1KwartierMetZone, "Lokaal", each DateTime.From([UTCMetZone]), type datetime),
        LokaalMinKwartier = Table.AddColumn(Lokaal, "LokaalMinKwartier", each DateTime.From([Min1KwartierMetZone])),
        Bewaren2 = Table.AddColumn(LokaalMinKwartier, "Bewaren2", each if [UTC]=#datetime(2000,1,1,0,0,0) then true else ([Lokaal]-[UTC])<>([LokaalMinKwartier]-[UTCMin1Kwartier])),
        KolommenVerwijderd3 = Table.RemoveColumns(Bewaren2,{"UTCMin1Kwartier", "UTCMetZone", "Min1KwartierMetZone", "LokaalMinKwartier"}),
        DSTSwitches = Table.SelectRows(KolommenVerwijderd3, each ([Bewaren2] = true)),
        KolommenVerwijderd4 = Table.RemoveColumns(DSTSwitches,{"Bewaren2"})
    in
        KolommenVerwijderd4 


    Wednesday, March 22, 2017 11:03 AM

All replies

  • Hi Chris,

    Well, it can be done, with some time investment.

    The code below gives you a list with all UTC and local time stamps in the period 1/1/2000 through 12/31/2027 on which the clock changes.

    Just run this query in an Excel file, save the file with the name of the current timezone, change the timezone on your computer, go out and back in Excel again, open the file with the query, run the query again, save the file with the name of the timezone etcetera, etcetera for all timezones (you require).

    Now you can combine the results and you have 1 big lookup file for all (your) timezones, that allows converting local times in each zone to the local time in another zone.

    All you have to do now is keep an eye on the timezone updates from Microsoft and refresh your files when appropriate. Coincidentally, an update just came in yesterday.

    I created the code with the Dutch version of Power BI Desktop some time ago when I was not yet very experienced with Power Query.

    let
        Bron = {36527..46753},
        UTC1Nummers = Table.FromList(Bron, Splitter.SplitByNothing(), {"UTC1"}, null, ExtraValues.Error),
        UTC2Nummers = Table.AddColumn(UTC1Nummers, "UTC2", each [UTC1] - 1),
        NummersNaarDatumTijd = Table.TransformColumnTypes(UTC2Nummers,{{"UTC1", type datetime}, {"UTC2", type datetime}}),
        UTC1MetZone = Table.AddColumn(NummersNaarDatumTijd, "UTC1+Zone", each DateTime.AddZone([UTC1],0,0)),
        UTC2MetZone = Table.AddColumn(UTC1MetZone, "UTC2+Zone", each DateTime.AddZone([UTC2],0,0)),
        Lokaal1 = Table.AddColumn(UTC2MetZone, "Lokaal1", each DateTime.From([#"UTC1+Zone"])),
        Lokaal2 = Table.AddColumn(Lokaal1, "Lokaal2", each DateTime.From([#"UTC2+Zone"])),
        Bewaren = Table.AddColumn(Lokaal2, "Bewaren", each if [UTC1]=#datetime(2000,1,2,0,0,0) then true else ([Lokaal1]-[UTC1])<>([Lokaal2]-[UTC2])),
        KolommenVerwijderd1 = Table.RemoveColumns(Bewaren,{"UTC2", "UTC1+Zone", "UTC2+Zone", "Lokaal1", "Lokaal2"}),
        GefilterdeDatums = Table.SelectRows(KolommenVerwijderd1, each ([Bewaren] = true)),
        PerKwartier = Table.AddColumn(GefilterdeDatums, "UTC", each {(Int64.From([UTC1])-1)*96..Int64.From([UTC1])*96}),
        KolommenVerwijderd2 = Table.RemoveColumns(PerKwartier,{"UTC1", "Bewaren"}),
        PerKwartierUitgevouwen = Table.ExpandListColumn(KolommenVerwijderd2, "UTC"),
        GedeeldDoor96 = Table.TransformColumns(PerKwartierUitgevouwen, {{"UTC", each _ / 96, type number}}),
        UTCMin1Kwartier = Table.AddColumn(GedeeldDoor96, "UTCMin1Kwartier", each [UTC] - 1 / 96, type number),
        KwartierenNaarDatumTijd = Table.TransformColumnTypes(UTCMin1Kwartier,{{"UTC", type datetime}, {"UTCMin1Kwartier", type datetime}}),
        UTCMetZone = Table.AddColumn(KwartierenNaarDatumTijd, "UTCMetZone", each DateTime.AddZone([UTC],0,0)),
        Min1KwartierMetZone = Table.AddColumn(UTCMetZone, "Min1KwartierMetZone", each DateTime.AddZone([UTCMin1Kwartier],0,0)),
        Lokaal = Table.AddColumn(Min1KwartierMetZone, "Lokaal", each DateTime.From([UTCMetZone]), type datetime),
        LokaalMinKwartier = Table.AddColumn(Lokaal, "LokaalMinKwartier", each DateTime.From([Min1KwartierMetZone])),
        Bewaren2 = Table.AddColumn(LokaalMinKwartier, "Bewaren2", each if [UTC]=#datetime(2000,1,1,0,0,0) then true else ([Lokaal]-[UTC])<>([LokaalMinKwartier]-[UTCMin1Kwartier])),
        KolommenVerwijderd3 = Table.RemoveColumns(Bewaren2,{"UTCMin1Kwartier", "UTCMetZone", "Min1KwartierMetZone", "LokaalMinKwartier"}),
        DSTSwitches = Table.SelectRows(KolommenVerwijderd3, each ([Bewaren2] = true)),
        KolommenVerwijderd4 = Table.RemoveColumns(DSTSwitches,{"Bewaren2"})
    in
        KolommenVerwijderd4 


    Wednesday, March 22, 2017 11:03 AM
  • Thank you Marcel! I didn't realise Microsoft had a team working on DST changes... It's a shame that there isn't a function in M to simplify this process.

    Hopefully someone from the Power Query team is reading...?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, March 22, 2017 11:12 AM
  • Just watch this "Artist Impression" (some Dutch included, but the message will be clear).
    Wednesday, March 22, 2017 11:36 AM
  • It should be noted that such conversions become highly unreliable for dates prior to 2007 (you can see the history in Microsofts blog start at 2007).

    Imagine if you want to convert date/times during the entire date range of Power Query (1/1/001 thru 12/31/9999).

    Even after 2007 there are some glitches as some countries take last minute decisions to (not) adjust clocks or they suspend DST temporarily during Ramadan like Egypt and Morocco back in 2010.

    As far as I know the leading source for global date and times is https://www.timeanddate.com/ with lots of information including the latest updates on https://www.timeanddate.com/news/time/

    Wednesday, March 22, 2017 11:49 AM