Transform timezone with DQL

elenaperez — Fri, 17 Apr 2026 09:30:25 GMT

Hi team

We have noticed that the dt.system.events are ingested with a different time zone. Is it possible to transform the timezone with DQL here?

I tried using bin(timestamp,24h,at:-1h), but with the change of hour, it gets desynchronized.
Thanks in advance!

Re: Transform timezone with DQL

t_pawlak — Fri, 17 Apr 2026 10:49:42 GMT

Hi,
Yes, it is possible in DQL, but not by using bin(timestamp, 24h, at: ...).

bin(..., at: -1h) behaves like a fixed offset, so it does not adjust for daylight saving time. That’s why it becomes misaligned after the time change.

A better approach is to build the local day using timezone-aware functions, for example:

fetch dt.system.events | filter event.kind == "BILLING_USAGE_EVENT" AND in( event.type, array( "Full-Stack Monitoring", "Infrastructure Monitoring", "Foundation & Discovery", "Mainframe Monitoring" ) ) | dedup event.id | fieldsAdd billed_units = coalesce( toDouble(billed_gibibyte_hours), toDouble(billed_host_hours), toDouble(billed_msu_hours) ) | fieldsAdd unit_price_eur = if(event.type == "Infrastructure Monitoring", toDouble(1), else: if(event.type == "Foundation & Discovery", toDouble(1), else: if(event.type == "Full-Stack Monitoring", toDouble(1), else: if(event.type == "Mainframe Monitoring", toDouble(1), else: null)))) | fieldsAdd estimated_cost_eur = billed_units * unit_price_eur | fieldsAdd local_year = getYear(timestamp, timezone:"Europe/Madrid") | fieldsAdd local_month = getMonth(timestamp, timezone:"Europe/Madrid") | fieldsAdd local_daynum = getDayOfMonth(timestamp, timezone:"Europe/Madrid") | fieldsAdd local_day = timestamp( year: local_year, month: local_month, day: local_daynum, hour: 0, minute: 0, second: 0, timezone: "Europe/Madrid" ) | summarize `Application and Infrastructure Observability` = sum(estimated_cost_eur), by: { event.type, local_day } | sort local_day asc

Then you can aggregate by local_day.
For display purposes, you can also use:

formatTimestamp(timestamp, timezone:"Europe/Madrid")

topic Transform timezone with DQL in DQL

Transform timezone with DQL

Re: Transform timezone with DQL