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?

Answer 1

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

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

Answer 3

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.

Answer 4

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")