Log EventTimestamp in ISO format

[ahmelsayed]

Currently it's logged as 10/16/2018 01:01:06.887 AM and the kusto client can't order AM vs PM properly. It mixes them. Ideally we would log in ISO "O" or ""yyyy-MM-dd'T'HH:mm:ss zzz" format

[soninaren]

@ahmelsayed how critical is this? This be a breaking change and would have potential impact on the analytics team. Need to understand this in more detail before we decide to make this change.

[ahmelsayed]

It's extremely annoying, but has a workaround of

| extend actualActualTimeStamp = make_datetime(EventTimestamp)
| order by actualActualTimeStamp asc

[soninaren]

@jcookems we are trying to assess if we can take this change. Need your help to asses the impact of the change on our existing analytics.

[jcookems]

Analytics cannot use the column currently because it is not ISO format. I would like this fix.

Please feel free to ask the Analytics team next time you are concerned a fix might affect us.

[soninaren]

@jcookems would using the syntax below make the column usable or are there additional constrains that prevent Analytics from using it.

| extend actualActualTimeStamp = make_datetime(EventTimestamp)
| order by actualActualTimeStamp asc

[TurtleBeach]

@jcookems @soninaren while I don't have a dog in this fight (figuratively speaking) I would ask that both sides settle on how the ISO string is formatted when the time stamp happens to fall on an exact even second boundary (in the context of the time resolution of the system). In the past I was burned (granted due to some quick and dirty sloppy parsing on my part) because a time stamp string returned as part of a table query of a Date field (NOT the time stamp managed directly by Azure) did NOT include .000 when the time was an even second. Other trailing zeroes were included (e.g. ... 13.330 or ... 15.100).
My .02

[jcookems]

My issue was not so much with ISO 8601 but with formats that don't work with todatetime/make_datedate. (The docs imply those won't work with non-ISO8601 formats.) Those Kusto functions work with the "AM/PM" suffixes, but some events emitted by HostVersion 1.0.12529.0 don't follow that: they either end with a space or "a. m./p. m."

let StartDateTime = datetime(2019-07-17T13:30:00);
let EndDateTime = StartDateTime + 10m;
FunctionsLogs
| where TIMESTAMP >= StartDateTime and TIMESTAMP < EndDateTime
| extend X = replace('07/17/2019', 'MM/DD/YYYY', EventTimestamp)
| extend X2 = replace(':\\d\\d:\\d\\d.\\d\\d\\d', ':mm:SS.ddd', X)
| summarize count() by X2, HostVersion

X2	HostVersion	count_
MM/DD/YYYY 01:mm:SS.ddd PM	1.0.12491.0	340799
MM/DD/YYYY 01:mm:SS.ddd PM	1.0.12529.0	49236333
MM/DD/YYYY 01:mm:SS.ddd	1.0.12529.0	219174
MM/DD/YYYY 01:mm:SS.ddd p. m.	1.0.12529.0	563
MM/DD/YYYY 01:mm:SS.ddd PM	2.0.12490.0	129599
MM/DD/YYYY 01:mm:SS.ddd PM	2.0.12507.0	103555
MM/DD/YYYY 01:mm:SS.ddd PM	2.0.12562.0	66868084

The highlighted formats do not parse properly.

let StartDateTime = datetime(2019-07-17T13:30:00);
let EndDateTime = StartDateTime + 10m;
FunctionsLogs
| where TIMESTAMP >= StartDateTime and TIMESTAMP < EndDateTime
| where EventTimestamp endswith " "
| limit 1
| project PreciseTimeStamp, EventTimestamp, todatetime(EventTimestamp)

PreciseTimeStamp	EventTimestamp	EventTimestamp1
2019-07-17 13:35:00.0249693	07/17/2019 01:35:00.019	2019-07-17 01:35:00.0190000

The parsed hour is incorrect. It should be "13" not "01". Likewise:

let StartDateTime = datetime(2019-07-17T13:30:00);
let EndDateTime = StartDateTime + 10m;
FunctionsLogs
| where TIMESTAMP >= StartDateTime and TIMESTAMP < EndDateTime
| where EventTimestamp endswith " p. m."
| limit 3
| project PreciseTimeStamp, EventTimestamp, todatetime(EventTimestamp)

PreciseTimeStamp	EventTimestamp	EventTimestamp1
2019-07-17 13:35:08.2611252	07/17/2019 01:35:08.255 p. m.	missing

Again, the parsed timestamp is not correct.

[ahmelsayed]

any updates on this?

[soninaren]

Moving this to triaged. Should be picked up during sprint planning.

[ankitkumarr]

Fixed by b768710