Timezone-aware queries in Azure Data Explorer

Question

I use the Azure Data Explorer to store temperature sensor values. The timestamps are in UTC. I want to aggregate these values by day for the last 7 days. Nevertheless, I want to use the local time from where these values came from and aggregate by the timestamps in local time (e.g. midnight would be at 00:00+2h and 22:00UTC). How can I do this with Kusto Query Language in the ADX?

Answer 1

E.g. if you want to provide the timezone UTC+1, you can extend your Kusto query by this:

| extend Timestamp = Timestamp + 3600s

Your filters for a time range would still need to be provided in UTC though.

Answer 2

Offsets work, but you cannot simply use a fixed offset for time zone if you care about daylight saving and having a very general solution. If you're doing something that regularly produces reports AND the time zone must be correct, read on.

It felt like a bit of a hack, but the way we achieved something along these lines was to create a time zone table with columns like this:

BeginOfDay: datetime(2020-01-01 00:00:00)
Timezone: "Africa/Addis_Ababa"
UTCStart: datetime(2020-01-01 00:00:00)-3h
UTCEnd: datetime(2020-01-02 00:00:00)-3h

There should be one row for every combination of time zone and day of interest. We populated something like ten years into the future. If you're worried about storage space or speed you only need to include the date range and time zones you care about, but even with 'everything' it was not a very large table.

Each row contains the 'day' BeginOfDay, which is always midnight and equivalent to "The first of January, 2020", and then the start and end of that local day in, in UTC time. We wrote a program to generate the contents of the table, of course.

After that, you can do something like:

let TimezoneDay = datatable (BeginOfDay:datetime, Timezone:string, UTCStart:datetime, UTCEnd:datetime)
    [datetime(2020-01-01), "Africa/Addis_Ababa", datetime(2019-12-31 21:00:00), datetime(2020-01-01 21:00:00), 
     datetime(2020-01-02), "Africa/Addis_Ababa", datetime(2020-01-01 21:00:00), datetime(2020-01-02 21:00:00), 
     datetime(2020-01-03), "Africa/Addis_Ababa", datetime(2020-01-02 21:00:00), datetime(2020-01-03 21:00:00)
     ];
let TemperatureEvents = datatable (Timestamp:datetime, Device:string, Temperature:real) 
    [datetime(2020-01-01 05:00:00), "Device 1", 10.5,
     datetime(2020-01-01 07:00:00), "Device 1", 30.5,
     datetime(2020-01-02 01:50:00), "Device 1", 24.0,
     datetime(2020-01-02 20:00:00), "Device 1", 20.5,
     datetime(2020-01-02 23:50:00), "Device 1", 19.5,
     datetime(2020-01-01 10:20:00), "Device 2", 0.5
    ];
TimezoneDay
| where Timezone == "Africa/Addis_Ababa"
// Use a dummy column to emulate a cross join
| extend dummy=1
| join kind=inner (TemperatureEvents | extend dummy = 1) on dummy
// Filter values into local time
| where Timestamp between (UTCStart .. UTCEnd)
| summarize AverageTemp=avg(Temperature) by BeginOfDay, Timezone, Device

The cross join may be a little expensive if you have a large dataset, but this is a starting point - you can also do a time window join to restrict the number of events you consider for each 'day'.

Answer 3

Azure Data Explorer doesn't have any built-in functions for converting between time zones.

The documentation recommends:

... Should time zone values be required to be kept as a part of the data, a separate columns should be used (providing offset information relative to UTC).

Thus, you should store two values - The original UTC-based timestamp so you can properly order the data, and the date from the local time zone so you can aggregate by local day.