I have a KQL-query in my SquaredUp for Azure dashboard that looks like this:
AppMetrics
| 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?
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
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…)
Oops, I didn’t look good enough at the results (it’s still early in the morning… ). 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.
). The result is that all the rows in the grid have the same timestamp:
