Kusto Query Lanquage: convert timestamp to local time

I have a KQL-query in my SquaredUp for Azure dashboard that looks like this:

| where Name == “GetProductContent AvgDurationMs”
| extend Localtime = TimeGenerated + 1h
| project format_datetime(Localtime,‘dd-MM-yyyy HH:mm:ss’), AppRoleName, Sum

To get the local time I have to manually add 1 hour to the TimeGenerated field in the winter and add 2 hours in the summer… KQL has no function to do this automatically and I can’t find a (simple) solution on the internet.
Has anyone a good idea?

Hi John

If you’re using a Grid tile to display the results from the query, you could potentially use a mustache template in the TimeGenerated column editor (in the Grid Columns panel) to use JavaScript’s knowledge of the current local time. From what I can find out KQL expects the client to do any time zone conversions.

If I edit the TimeGenerated column and use this template, I get the local time string in the Grid:

{{(Date(value * 1000)).toString()}}

I had to change my timezone for this because annoyingly we’ve just moved back into GMT here in the UK :frowning:

Hai Masokisi,
That seems to work! Thnx!

But why multiply with 1000? What is the logic behind this?
Next step is to format ‘Thu Nov 04 2021 08:48:59 GMT+0100 (Midden-Europese’ to ‘04-11-2021 08:48:59’. Is this possible with the Javascript/Mustache combination? (I’m not an expert on this…)

John J.

Oops, I didn’t look good enough at the results (it’s still early in the morning… :wink:). The result is that all the rows in the grid have the same timestamp:


Oh dear, yes I see I missed that too. Date() just returns the current Date and I guess isn’t equivalent to new Date() like I assumed (and which the Grid mustache won’t allow me to use unfortunately). Back to the drawing board I guess. You could use this to get the right date I think:

{{timeago(value, true, true)}}

Unfortunately I don’t know how to get it into ISO Date format though. Sorry.

Unfortunately, this doesn’t work either:

It’s an hour early and the date should be November 5…
Never mind, thnx for the effort! We’ll keep looking for a solution…

John J.