How do I convert #datetime to my local timezone?

Question

A column in Power Query converts a JavaScript timezone into UK format:

= #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]/1000)

But this produces the wrong output (-1hr difference), presumably due to UTC / GMT+Summertime differences?

I tried to convert again using the following code:

= DateTimeZone.ToLocal(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]/1000))

But I get the error:

Expression.Error: We cannot convert the value #datetime(2019, 7, 11, 23, 11, 40) to type DateTimeZone.
Details:
    Value=11/07/2019 23:11:40
    Type=Type

How do I do this properly?

Edit:

Timestamp = Text.BetweenDelimiters([ReceivedTime], "(", ")"), type text

where [ReceivedTime] is a JSON date in milli-seconds like: /Date(1562886700000)/

There may be a more sensible way to convert a JSON date without resorting to Text methods.

The resulting solution should display correctly in Power BI reports - the reports should display all datetimes in the user's Local Timezone.

Connett

have you tried converting your date time to date time zone using DateTimeZone.From() before passing it to the DateTimeZone.ToLocal() function – Impassive

That seems to partially work, thanks! - my column now has a +01:00 in it. What is now confusing me is that despite the column having the +1, it is still showing in UTC time (without +1) on my Power BI report. Any ideas? I used this: DateTimeZone.ToLocal(DateTimeZone.From(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]/1000))) – Connett

is [Timestamp] date time ? and why is it divided by 1000 – Impassive

Good question - have updated Question with more info. – Connett

Sorry but I have no idea how to solve this problem. If you don't get any responses here, maybe you can try posting this problem on the Power BI community forums. – Impassive

Answer 1

DateTimeZone.ToLocal takes a datetimezone parameter, but you only have a datetime. A different function DateTimeZone.From can convert a datetime to datetimezone but it assumes the input datetime is in the local zone, which converts it the opposite direction.

Instead, you can call the #datetimezone constructor with UTC time zone, then convert it to your local zone:

= DateTimeZone.ToLocal(#datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]/1000))

Gotcher

Answer 2

Here's a quick way to transform a date to your daylight savings time:

let
  UTC_DateTimeZone = DateTimeZone.UtcNow(), 
  UTC_Date         = Date.From(UTC_DateTimeZone), 
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday), 
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday), 
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1, 
  CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
  CET_Timezone

Source: https://gorilla.bi/power-query/last-refresh-datetime/

In case you have a time that is UTC, this script works flawlessly.You can simply replace the UTC_DateTimeZone step with the DateTime value you're working with.

DateTimeZone.SwitchZone : https://powerquery.how/datetimezone-switchzone/

DateTimeZone.UtcNow: https://powerquery.how/datetimezone-utcnow/

Lactose