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

Azure – KQL datetime formatting

I have a column,['timestamp'] in Azure Data Explorer. A certain value, x, is assigned to this timestamp.

Data is being streamed in real time and I would like to take the last value of yesterday’s data.
However, when I do a substring() or datetime formatting, for some reason, it changes the date from 2022-08-24 to 2022-08-23.

Code:

datasource
| extend ts1 = (datetime_add('day',-1, todatetime(timestamp)))
| extend ts2 = substring(ts1,0,10)
| extend ts3 = format_datetime(todatetime(ts1), 'yyyy-MM-dd')
| project timestamp, ts1,ts2,ts3
| sort by timestamp desc

Output:

timestamp ts1 ts2 ts3
2022-08-25 10:56:09.000 2022-08-24 10:56:09.000 2022-08-24 2022-08-24
2022-08-25 08:56:09.000 2022-08-24 08:56:09.000 2022-08-24 2022-08-24
2022-08-25 07:26:09.000 2022-08-24 07:26:09.000 2022-08-23 2022-08-23
2022-08-25 05:56:09.000 2022-08-24 05:56:09.000 2022-08-23 2022-08-23

Required output:
ts2 and ts3 to display 2022-08-24 instead of 2022-08-23

Please review my code and output and help pls.

Tags:

3

Answers


  1. 0

    We’ll do it in two parts 🙂

    Part 1: Understanding the issue.

    The ADX documentation states:

    "A datetime value in Kusto is always in the UTC time zone"

    That means that all datetime operations are done in UTC.
    The results are then converted by your client tool, for displaying purposes, to the time zone you chose.

    The demonstration below was done in Azure Web Explorer, set to Australia/West time zone (UTC+8).

    As you can see, while in the local TZ, all timestamps occur at 2022-08-25, in UTC two of the timestamps occur at 2022-08-25 but the other two occur at 2022-08-24.
    When we retrieve the start of the day (regardless of the exact method), the operation is applied to the UTC values, therefore for two of the timestamps the start of day is 2022-08-25 00:00:00 and for the other two, it is 2022-08-24 00:00:00.
    Only then the result is being converted to the client tool TZ, by adding 8 hours to the results.

    let datasource = datatable (timestamp:datetime)
[
     datetime("2022-08-24 21:56:09.000")
    ,datetime("2022-08-24 23:26:09.000")
    ,datetime("2022-08-25 00:56:09.000")
    ,datetime("2022-08-25 02:56:09.000")
]; 
datasource
| project   local_TZ            = timestamp
           ,UTC                 = datetime_local_to_utc(timestamp, "Australia/West")
           ,UTC_startofday      = datetime_local_to_utc(startofday(timestamp), "Australia/West")
           ,local_TZ_startofday = startofday(timestamp)

    issue results

    Fiddle

    Login or Signup to reply.
  2. 0

    Part 2: Solution

    Since all datetime values are stored in UTC and manipulated as UTC, we will use datetime_utc_to_local() to convert our timestamps, so that the values we see in our local TZ display, will now be the actual UTC values.
    In our case we use the function with Australia/West, so 8 hours are being added to the timestamps.

    Please note that in the ADX Web Explorer the results (utc_to_local column) look strange since the GUI does an additional transformation and adds another 8 hours.
    But remember – the UTC values are what counts.

    Now that the timestamps are set to the right UTC values, we can use the startofday().
    Once again, the GUI adds another 8 hours to the results, and therefore we get 2022-08-25 08:00:00.

    Once you’re done with your calculations, you would probably want to use datetime_local_to_utc() to convert the results back to UTC.

    let datasource = datatable (timestamp:datetime)
[
     datetime("2022-08-24 21:56:09.000")
    ,datetime("2022-08-24 23:26:09.000")
    ,datetime("2022-08-25 00:56:09.000")
    ,datetime("2022-08-25 02:56:09.000")
]; 
datasource
| project   local_TZ                = timestamp
           ,utc_to_local            = datetime_utc_to_local(timestamp, "Australia/West")
| extend    utc_to_local_startofday = startofday(utc_to_local)
| extend    utc                     = datetime_local_to_utc(utc_to_local_startofday, "Australia/West")

    Solution Results

    Fiddle

    Login or Signup to reply.
  3. 0

    Two comments regarding the original code:

    • Since timestamp is already of datetime type, using todatetime(timestamp) has no effect.
    • While the use of datetime_add() is valid, the same result can be simply achieved with arithmetic operators over timespans, e.g. timestamp - 1d
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search