I have millions of records in Azure Data Explorer. Each of this record has a timestamp value associated with it. I want to be able to convert this timestamp value in the specific time zone.

For example in SQL I use AT TIME ZONE to convert timestamp value from one zone into another -

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

I am not willing to use offset value as it doesn't support daylight saving changes.

How I can do this with Kusto query language in ADX?

CodePudding user response：

Luckily, US East Time is relatively a simple case.
The code below is valid for dates in the range of 2007 and beyond.

In the United States Daylight Saving Time begins at 2:00 a.m. local time on the second Sunday in March.
On the first Sunday in November areas on Daylight Saving Time return to Standard Time at 2:00 a.m.
When Daylight Saving Time begins in the United States turn your clocks ahead one hour. At the end of Daylight Saving Time turn your clocks back one hour.

https://www.timetemperature.com/tzus/daylight_saving_time.shtml

// 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
let et2utc_mar_2nd_sun_2am  = (dt_utc:datetime){make_datetime(getyear(dt_utc),3,1,2,0,0)   (7d - dayofweek(make_datetime(getyear(dt_utc),3,1)))}   7d   5h};
let et2utc_nov_2nd_sun_2am  = (dt_utc:datetime){make_datetime(getyear(dt_utc),11,1,2,0,0)   (7d - dayofweek(make_datetime(getyear(dt_utc),3,1)))}   4h};
let EasternTimeOffset       = (dt_utc:datetime){iff(dt_utc between (et2utc_mar_2nd_sun_2am(dt_utc) .. et2utc_nov_2nd_sun_2am(dt_utc)), -4h, -5h)};
t
| extend EasternTimeOffset = EasternTimeOffset(dt_utc)
| extend EasternTime = dt_utc   EasternTimeOffset

Fiddle

CodePudding user response：

Based on the Python plugin

pandas relevant documentation: tz_localize, tz_convert & strftime

// Sample generation. Not part of the solution
let t = materialize(range i from 1 to 15 step 1 | extend dt_utc = bin(ago(rand()*365d*10), 1s));
// Solution Starts here
let p_schema = typeof(*, dt_et:datetime);
let p_arguments = dynamic({"TZ" : "US/Eastern"});
let p_script =
```
iso_fmt = "%Y-%m-%d %H:%M:%S"
result = df
result["dt_et"] = df["dt_utc"].dt.tz_localize("UTC").dt.tz_convert(kargs["TZ"]).dt.strftime(iso_fmt)
```;
t
| evaluate  python(p_schema, p_script, p_arguments)
| extend    offset = dt_et - dt_utc