Power Query：日期时间区域操作

Question

我正在基于 SQL Server 表 User 中的数据构建 Power BI 报告，该表包含一个类型为 DateTypeOffset(7) 的 CreatedDate 列。我正在寻找与 .NET [TimeZoneInfo.FindSystemTimeZoneById] 等效的 Power Query。我的报告数据具有存储为标准 ID（例如 Pacific Standard Time）的时区信息，我需要将 DateTimeOffset 列中的值转换为上述时区的时间，这是我在 C# 中实现的方法：

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

这使我得出了“用户创建时间”存储的时区与UTC之间的时间差。因为上面的代码的目的是考虑夏令时等时区特征，以找出当前偏移量，并且我只需执行该行代码一次即可对我的User表中的所有行进行操作。

现在，我可以通过使用[DateTimeOffset.ToOffset]在我的C#代码中将该偏移量（可能为正数或负数）添加到User.CreateDateTime列中的每个值中：

DateTimeOffset convertedOffset = UserCreatedDateTime.ToOffset(timeZoneOffsetSpan)

我需要在我的Power BI报告中进行相同的转换，但是我找不到任何Power Query函数可以像[TimeZoneInfo.FindSystemTimeZoneById]一样完成相同的工作。如果存在该函数，则将使用它来替代我的C#代码首行，然后我需要找到与[TimeZoneInfo.FindSystemTimeZoneById]等效的函数。[DateTimeZone.SwitchZone]可能是我需要的，但是我首先需要知道对应于我的时区的偏移量，然后才能将该偏移量作为第二个和第三个参数提供给该函数。

因此，最终我需要找到Power BI的类似功能来实现[TimeZoneInfo.FindSystemTimeZoneById]。 有人能帮帮我吗？

- AverageAsker

---

很遗憾，Power BI中没有一个datetime_format可以将datetimeoffset格式化为本地时间。截至2019年3月，datetimeoffset在Power BI中仅以隐藏偏移部分的形式显示。 - Nick.McDermaid

Answer 1

您可以利用 TSQL 的 AT TIME ZONE 功能为每个时区的每一天创建一个偏移量表（以分钟为单位），并将结果表导入 PowerBI，这样您就可以添加/减去相关偏移量到基准时间。

如果我处于 OP 的情况下，我会将所有时间转换为 UTC，并在 PowerBI 中根据您想要显示的时区添加偏移量。（也许使用 this 方法？）

以下示例提供了一个表格，其中包含澳大利亚时区相对于 UTC 的偏移量。

如果我的目标表格是 UTC，并且我想在 PowerBI 中更改时间，我只需要添加特定的偏移量到我的时间中...

+------------+-----------+----------+----------+----------+ 
| 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 |
+------------+-----------+----------+----------+----------+

您可以看到4月1日 SA 的偏移量正在改变

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 

如果没有日期或时区的限制，显然表格会变得非常大。

- Trubs

Answer 2

您可以向数据模型添加一个表格，其中包含时区ID和时区偏移量列。在主查询中，您可以加入此表格，然后创建一个计算来将偏移量加或减到新列中。之后，删除已加入的列。

请参见此处如何在Power Query中执行AddHours的等效操作？

或者您可以使用M语言中内置的DateTimeZone函数。

- teylyn

---

如果我使用那个“自制”的带有区域偏移的表格，那么我的报告每年会有两次错误（我无法承受），这是因为夏令时的缘故，所以，我们不应该使用硬编码的偏移量——这是一种有缺陷的方法，因为该偏移量每年至少会有两次错误——当我们进入夏令时和退出夏令时时。时区不能仅通过知道偏移量来计算，但如果已知时区，则可以计算出偏移量（详见此处：http://stackoverflow.com/tags/timezone/info）。至于DateTimeZone函数——正如我在原问题中所示，它们并没有帮助。 - AverageAsker