Extract Date from the StartTime without UTC format in Azure workbook

Question

Hi,

I’m building an Azure Monitor Workbook to track data processing. The logs in our Log Analytics workspace are stored in UTC, but when I run KQL inside the workbook the timestamps render in the viewer’s local time. I need a daily summary that combines rows from two independent tables, and there’s no natural common key. To create a per‑day key, I tried extracting a Date from StartTime, but it ends up in UTC and doesn’t match the locally rendered times. What’s the best way to get the exact local date (without hard‑coding a specific time zone), or an alternative approach to reliably group by day and join the two tables?

To parse the date from the StartTime ", using below line... but it is storing Date in a UTC format

Answer 1

Hello Aswini,

Thanks for raising this question in Q&A forum.

I understand you are facing a mismatch where KQL processes data in UTC, but the Azure Workbook UI renders timestamps in the user's local time. When you try to join tables based on the "Date," the UTC date boundary (00:00 UTC) often differs from the local date boundary, causing rows to fall into the "wrong" day buckets relative to what you see on screen.

Since KQL runs on the backend (which is always UTC) and does not natively know the "Browser Timezone" of the specific user viewing the Workbook, you cannot dynamically detect the viewer's timezone inside the query without passing it as a parameter.

Here is the best approach to handle this without hard-coding a specific timezone in the query text itself:

Solution: Use a Workbook Parameter for Timezone Offset

1. Create a Parameter:
   • In your Workbook, click Add parameters.
   • Create a new parameter (e.g., named TimeOffset).
   • Type: Dropdown (or Text).
   • Values: You can populate this with offsets like -5h, +5.5h (for IST), +0h, etc. Let the user select their region at the top of the report.
2. Update Your KQL to Use the Parameter:
   • Inject this parameter into your query to shift the UTC time before formatting it into a string.

<!-- end list -->

// {TimeOffset} comes from your Workbook parameter

// If using a static value for testing, replace with: let TimeOffset = 5.5h;

let TimeOffset = time({TimeOffset}); 

Table1

| extend LocalStartTime = StartTime + TimeOffset

| extend DayKey = format_datetime(LocalStartTime, 'yyyy-MM-dd')

| join kind=inner (

    Table2

    | extend LocalStartTime = StartTime + TimeOffset

    | extend DayKey = format_datetime(LocalStartTime, 'yyyy-MM-dd')

) on DayKey

Why this works:

• StartTime + TimeOffset: This mathematically shifts the timestamp to align with the local clock (e.g., changing 23:00 UTC on Monday to 04:30 on Tuesday).
• format_datetime(..., 'yyyy-MM-dd'): This extracts the date portion after the shift, ensuring the "Day" bucket matches the local calendar day.
• Joins: By applying the same logic to both tables, the DayKey will match perfectly across them for that specific timezone.

If you cannot use a parameter and just want a quick fix for a specific region (e.g., EST), you can hardcode the offset (e.g., -5h) directly in the extend line, but the parameter approach makes the Workbook usable globally.

If helps, approve the answer.

Best Regards,

Jerald Felix