Convert UTC 'TimeGenerated' to local time in Azure monitor/log/analytics, when using "summarize by"

Question

I have this simple query

MyLog
| summarize  avg(executionTimeInMS_d) by bin(TimeGenerated, 5min)

I'd like the summary to be in my local time zone, not UTC. This does not work :

MyLog
| summarize  avg(executionTimeInMS_d) by bin(TimeGenerated-5, 5min)

Can this be done?

Ese

Answer 1

There is now a "Display time zone" setting in the App Insights query page. This will convert the timestamp to the selected timezone. It will also show the timezone in the timestamp column heading.

This only seems to work if you output the timestamp directly. If you apply any operations it reverts to UTC.

Herrmann

Answer 2

datetime values are in UTC.

if you know the timezone offset (at the time you run the query), you can subtract/add it to your datetime values as explained here: https://learn.microsoft.com/en-us/azure/kusto/query/datetime-timespan-arithmetic

for example: print now() - 7h

Hanshansard

Answer 3

Best to convert using the datetime_utc_to_local() function. This way you can dynamically handle daylight savings within the query and don't need to depend on the UI.

AzureDiagnostics
| extend CentralTime = datetime_utc_to_local(TimeGenerated, "US/Central")

Gossipry

In Application Insights, I thought this function wasn't supported because the editor showed an error, "The name 'datetime_utc_to_local' does not refer to any known function." Turns out it is supported when you run a query though. For example: TimeLocal = datetime_utc_to_local(timestamp, "America/Los_Angeles"). Not perfectly supported, as the column name output for the above projection is TimeLocal [UTC], but I'll live. – Chiekochien

Answer 4

You can do this by subtracting/adding the time different from UTC. For example, to convert to EST. I subtracted 5h from TimeGenerated which is in UTC.

AppServiceConsoleLogs
| extend EasternTime = TimeGenerated - 5h
| sort by EasternTime desc
| project Level, EasternTime, ResultDescription

Demp