azure - 在 Azure ADX Kusto 查询中将 UTC 时间转换为特定时区

Question

我在 Azure 数据资源管理器中有数百万条记录。每个记录都有一个与之关联的时间戳值。我希望能够在特定时区转换此时间戳值。

例如，在 SQL 中，我使用 AT TIME ZONE 将时间戳值从一个区域转换为另一个区域 -

Select CONVERT(datetime, timestampvalueColumn) AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time' as 'TimeInEST' from Table;

我不愿意使用偏移值，因为它不支持夏令时更改。

如何使用 ADX 中的 Kusto 查询语言执行此操作？

Answer 1

嗯，Kusto 团队进展很快:-)

支持timezones添加了转换:

• datetime_local_to_utc()
• datetime_utc_to_local()

<小时/>

// Sample generation. Not part of the solution
let t = materialize(range i from 1 to 15 step 1 | extend dt_utc = ago(rand()*365d*10));
// Solution Starts here
t
| extend dt_et = datetime_utc_to_local(dt_utc, "US/Eastern")
| extend offset = dt_et - dt_utc

<表类=“s-表”><标题>我dt_utcdt_et偏移量 <正文>52012-12-03T17:24:51.6057076Z2012-12-03T12:24:51.6057076Z-05:00:00142012-12-10T05:04:17.8507406Z2012-12-10T00:04:17.8507406Z-05:00:00102013-03-23T14:42:00.4276416Z2013-03-23T10:42:00.4276416Z-04:00:00152013-10-01T06:28:36.4665806Z2013-10-01T02:28:36.4665806Z-04:00:00112017-07-18T06:10:30.9963876Z2017-07-18T02:10:30.9963876Z-04:00:0032017-11-17T21:57:58.4443366Z2017-11-17T16:57:58.4443366Z-05:00:0062018-05-09T03:36:24.7533896Z2018-05-08T23:36:24.7533896Z-04:00:00122018-06-05T17:36:41.7970716Z2018-06-05T13:36:41.7970716Z-04:00:0042018-08-03T16:25:19.9323686Z2018-08-03T12:25:19.9323686Z-04:00:0082019-02-21T17:33:52.9957996Z2019-02-21T12:33:52.9957996Z-05:00:0022020-09-24T18:37:08.0049776Z2020-09-24T14:37:08.0049776Z-04:00:0012020-12-09T19:57:23.7480626Z2020-12-09T14:57:23.7480626Z-05:00:0072021-01-17T13:42:55.0632136Z2021-01-17T08:42:55.0632136Z-05:00:0092021-03-04T23:44:01.7192366Z2021-03-04T18:44:01.7192366Z-05:00:00132022-06-04T16:26:57.8826486Z2022-06-04T12:26:57.8826486Z-04:00:00

Fiddle