Power Query: Date Time Zone operations

user_5340963 0
Viewed 4197

I am building Power BI report based on data from SQL Server's table User which contains a column CreatedDate of type DateTypeOffset(7). I am looking for Power Query equivalent to .NET [TimeZoneInfo.FindSystemTimeZoneById]. My Report data has a Time Zone info which is stored as standard ID (like Pacific Standard Time) and I need to convert values in my DateTimeOffset column to the time zone I was talking about above, this is how I do that in C#:

TimeSpan timeZoneOffsetSpan = TimeZoneInfo.FindSystemTimeZoneById(settings.TimeZ​one).GetUtcOffset(UserCreatedDateTime);

This gives me a difference between the time in the time zone stored in my Settings and UTC for UserCreatedDateTime. And I am executing that line of the code just once for all rows in my User table because the purpose of the code above is to find out current offset taking into account such timezone's features like DayLightSaving.

And now I can simply add that offset (which could be positive or negative) against every value in my User.CreateDateTime column, in C# I am doing that by using [DateTimeOffset.ToOffset]:

DateTimeOffset convertedOffset = UserCreatedDateTime.ToOffset(timeZoneOffsetSpan)

So I need to be able to do the same conversions in my Power BI report but I couldnot find any Power Query function which can do the same what [TimeZoneInfo.FindSystemTimeZoneById] does, if that function existed it would cover my first line of C# code and then I would need to find an equivalent to [TimeZoneInfo.FindSystemTimeZoneById]. [DateTimeZone.SwitchZone] is probably what I need but I first need to know the offset corresponding to my time zone and then I would be able to supply that offset as 2nd and 3rd parameters to that function.

So, to finalize, I need Power BI analog for [TimeZoneInfo.FindSystemTimeZoneById]. Can anybody help?

powerbi powerquery
2 Answers

You can take advantage of the AT TIME ZONE feature of TSQL to create a table of offsets (in minutes) for each day, for each timezone, and import the resultant table into PowerBI, so you can add/subtract relevant offsets to your base time.

If I was in the OP's situation, I would import (convert) all times to UTC, and then add the offset within PowerBI, dependent on the timezone you want to display. (maybe using this method?)

The example below gives a table the offsets for Australian timezones against UTC.

If my target table is in UTC and I want to change the time in PowerBI, I just have to add the specific offset to my time...

+------------+-----------+----------+----------+----------+ 
| TargetDate | QLDOffset | NTOffset | SAOffset | WAOffset |
+------------+-----------+----------+----------+----------+ 
| 2018-04-02 |      -600 |     -570 |     -570 |     -480 | 
| 2018-04-01 |      -600 |     -570 |     -570 |     -480 | 
| 2018-03-31 |      -600 |     -570 |     -630 |     -480 | 
| 2018-03-30 |      -600 |     -570 |     -630 |     -480 |
+------------+-----------+----------+----------+----------+

You can see the offset changing for SA on 1st April

WITH MostDays AS ( --MostDays is a table of 10 years worth of dates going back from today.  
               SELECT Today = CAST(DATEADD(DAY, -days, GETUTCDATE())  AS DATETIMEOFFSET)
               FROM (SELECT TOP 3650 Days = ROW_NUMBER() OVER (ORDER BY message_id )  -- this will give a rolling window of 10 years
                     FROM sys.messages
                    )x
            )
--  This section takes the datetime from above, and calculates how many minutes difference there is between each timezone for each date.
Select  TargetDate=CAST(Today AS DATE)
    ,QLDOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'E. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,NTOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'AUS Central Standard Time' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,SAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'Cen. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,WAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'W. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
FROM MostDays

Obviously the table gets pretty big if you have no limits on dates or timezones

user_26089200
Related