Transform timezone with DQL

[elenaperez]

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!

[t_pawlak]

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