Question 105794 in Azure Question posted in
The official documentation to get you started can be found here.

Convert UTC time to specific time zone in Azure ADX Kusto query

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?

Tags:

3

Answers


  1. 0

    Usually the answer is "Don’t do it in Kusto", do it in the client that is reading the results from Kusto, which most certainly will have a "utc-to-local-time" or "utc-to-this-timezone" functions.

    Login or Signup to reply.
  2. 0

    Well, the Kusto team is moving fast 🙂

    Support for timezones conversion has been added:

    • 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
    i dt_utc dt_et offset
    5 2012-12-03T17:24:51.6057076Z 2012-12-03T12:24:51.6057076Z -05:00:00
    14 2012-12-10T05:04:17.8507406Z 2012-12-10T00:04:17.8507406Z -05:00:00
    10 2013-03-23T14:42:00.4276416Z 2013-03-23T10:42:00.4276416Z -04:00:00
    15 2013-10-01T06:28:36.4665806Z 2013-10-01T02:28:36.4665806Z -04:00:00
    11 2017-07-18T06:10:30.9963876Z 2017-07-18T02:10:30.9963876Z -04:00:00
    3 2017-11-17T21:57:58.4443366Z 2017-11-17T16:57:58.4443366Z -05:00:00
    6 2018-05-09T03:36:24.7533896Z 2018-05-08T23:36:24.7533896Z -04:00:00
    12 2018-06-05T17:36:41.7970716Z 2018-06-05T13:36:41.7970716Z -04:00:00
    4 2018-08-03T16:25:19.9323686Z 2018-08-03T12:25:19.9323686Z -04:00:00
    8 2019-02-21T17:33:52.9957996Z 2019-02-21T12:33:52.9957996Z -05:00:00
    2 2020-09-24T18:37:08.0049776Z 2020-09-24T14:37:08.0049776Z -04:00:00
    1 2020-12-09T19:57:23.7480626Z 2020-12-09T14:57:23.7480626Z -05:00:00
    7 2021-01-17T13:42:55.0632136Z 2021-01-17T08:42:55.0632136Z -05:00:00
    9 2021-03-04T23:44:01.7192366Z 2021-03-04T18:44:01.7192366Z -05:00:00
    13 2022-06-04T16:26:57.8826486Z 2022-06-04T12:26:57.8826486Z -04:00:00

    Fiddle

    Login or Signup to reply.
  3. 0

    You can build a convenience function using a similar ideia of the function given bellow. Note that the conversion works for DST (Daylight Saving Time) as well. You just need a way to map a place to its timezone string. In the function that follows, the mapping is from a Brazilian state abbreviation to its timezone string.
    See the documentation for a list of available timezones.

    .create-or-alter function with (
    docstring = 'Dada uma UF e um datetime (UTC), retorna o horário local. Não é tratado o GMT-5 ao oeste de AM.'
) ToLocalDatetime(state: string, dtutc: datetime) {
    let selected_tz = iff('GO,DF,MG,ES,RJ,SP,PR,SC,RS' has state, 'America/Sao_Paulo',
        iff('MA,PI,CE,RN,PB' has state, 'America/Fortaleza',
        iff('AL,SE' has state, 'America/Maceio',
        iff('BA' == state, 'America/Bahia',
        iff('RR' == state, 'America/Boa_Vista',
        iff('MS' == state, 'America/Campo_Grande',
        iff('MT' == state, 'America/Cuiaba',
        iff('AM' == state, 'America/Manaus',
        iff('PA,AP' has state, 'America/Belem',
        iff('AC' == state, 'America/Rio_Branco',
        iff('RO' == state, 'America/Porto_Velho',
        iff('PE' == state, 'America/Recife',
        iff('TO' == state, 'America/Araguaina', '')))))))))))));
    let localdt = datetime_utc_to_local(dtutc, selected_tz);
    let dt_hr = split(format_datetime(localdt, "yyyy-MM-dd HH:mm:ss"), " ");
    iff(isnotempty(localdt), 
        strcat(dt_hr[0], "T", dt_hr[1], format_timespan(localdt - dtutc, "HH:mm")),
        '')
}

    A couple tests at the moment when DST ended in the DF Brazilian state:

    print(ToLocalDatetime('DF', datetime('2019-02-17 01:00:00')))

    Output: 2019-02-16T23:00:00-02:00

    print(ToLocalDatetime('DF', datetime('2019-02-17 02:00:00')))

    Outputs 2019-02-16T23:00:00-03:00

    I agree with other answers stating that is better to do it on the client side for most cases. Additionally, the iff sequence of the function is ugly. For a more elegant solution, it is possible to define a datatable such as:

    datatable(state:string, tz:string) [
        'GO,DF,MG,ES,RJ,SP,PR,SC,RS', 'America/Sao_Paulo',
        'MA,PI,CE,RN,PB', 'America/Fortaleza',
......

    However, if you to it you cannot use the function on some scenarios, due to documented restrictions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search