1

I receive the error format_datetime(): failed to parse format string in argument #2 when trying to format_datetime() using ISO8601 format yyyy-MM-ddTHH:mm:ss.fffZ.

If I leave the T and the Z out, it works,

Surely KQL can format datetimestamps in timezone-aware format. I'm just missing it. I read the docs and it appears that T and Z are not supported formats nor delimiters yet each example in the docs shows the T and Z present(?).

Example:

StorageBlobLogs
| where
    AccountName == 'stgtest'
| project
    TimeGenerated = format_datetime(TimeGenerated, 'yyyy-MM-ddTHH:mm:ss.fffZ'), //herein lies the issue
    AccountName,
    RequestBodySize = format_bytes(RequestBodySize)
| sort by TimeGenerated asc

If the code is changed to...

- `TimeGenerated = format_datetime(TimeGenerated, 'yyyy-MM-dd HH:mm:ss.fff')`

...it works, but this is not a timezone-aware timestamp (something I prefer to work in to reduce confusion).

ericOnline
  • 1,586
  • 1
  • 19
  • 54

2 Answers2

2

I ended up just using strcat to get the exact ISO8601 format I needed when I had to incorporate this format in a URI.

strcat(format_datetime(timestamp, 'yyyy-MM-dd'), 'T',
       format_datetime(timestamp, 'HH:mm:ss'), 'Z')

Not ideal. I would have preferred something quick like .NETs ToString("o"), but this works.

PatrickSteele
  • 14,489
  • 2
  • 51
  • 54
1

datetime_utc_to_local()
Timezones

I would highly recommend not doing that.
If possible, you would like to let the time zone & format being dealt on the client side.

All datetime values in KQL are UTC. Always.
Even the result of datetime_utc_to_local() is another UTC datetime.
That may lead to (what seems like) unexpected behavior of datetime manipulations (example).

StorageBlobLogs
| sample 10
| project TimeGenerated
| extend Asia_Jerusalem     = datetime_utc_to_local(TimeGenerated, "Asia/Jerusalem")
        ,Europe_London      = datetime_utc_to_local(TimeGenerated, "Europe/London")
        ,Japan              = datetime_utc_to_local(TimeGenerated, "Japan")
| extend Asia_Jerusalem_str = format_datetime(Asia_Jerusalem    ,"yyyy-MM-dd HH:mm:ss.fff")
        ,Europe_London_str  = format_datetime(Europe_London     ,"yyyy-MM-dd HH:mm:ss.fff")
        ,Japan_str          = format_datetime(Japan             ,"yyyy-MM-dd HH:mm:ss.fff")
| project-reorder TimeGenerated, Asia_Jerusalem, Asia_Jerusalem_str, Europe_London, Europe_London_str, Japan, Japan_str

Results

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • Thanks for the reply, though I'm not sure I follow what you're saying. I want to display the `TimeGenerated` column in the following format: `yyyy-MM-ddThh:mm:ss.fffZ` instead of the default format of `MM/d/yyyy h:mm:ss.ff AM/PM`. – ericOnline Oct 07 '22 at 00:34
  • I just went with ``TimeGenerated = format_datetime(TimeGenerated, 'yyyy-MM-dd HH:mm:ss.fff')` and forewent the `T` and `Z`. Not ideal but it works. – ericOnline Oct 07 '22 at 01:21