Skip to content

How to Handle Time Zones and DST

The 15m views expose both UTC and CET/CEST timestamps for every period.

Column Time zone Notes
begin_utc / end_utc UTC Standard reference. Always exactly 96 periods per day.
begin_cet / end_cet Europe/Berlin (CET in winter, CEST in summer) For market-aligned reporting.
delivery_date_utc UTC calendar date Derived from dim_period.start_date.

The rule

Aggregate in UTC. Label in CET.

UTC has 96 periods per day, every day. CET local clock-time has 92 periods on spring-forward day and 100 on fall-back day. Aggregating on local time produces lumpy hour buckets across the DST boundary.

Daylight saving transitions

Spring forward (last Sunday of March)

The local clock skips from 02:00 CET to 03:00 CEST. Locally, the 02:00–02:45 hour does not exist.

begin_utc begin_cet UTC offset
01:00 UTC 02:00 CET +1
01:15 UTC 02:15 CET +1
01:30 UTC 02:30 CET +1
01:45 UTC 02:45 CET +1
02:00 UTC 04:00 CEST +2 (skipped)
02:15 UTC 04:15 CEST +2

UTC still has 96 periods (00:00–23:45). CET has 92 distinct local labels.

Fall back (last Sunday of October)

The local clock repeats 02:00–02:45 CEST as 02:00–02:45 CET.

begin_utc begin_cet UTC offset
00:00 UTC 02:00 CEST +2
00:15 UTC 02:15 CEST +2
00:30 UTC 02:30 CEST +2
00:45 UTC 02:45 CEST +2
01:00 UTC 02:00 CET (repeated) +1
01:15 UTC 02:15 CET (repeated) +1

UTC has 96 periods. CET has 100 distinct (label, offset) pairs but the same local label twice for 02:00–02:45.

Group by UTC, display in CET

select
    delivery_date_utc,
    begin_utc,
    min(begin_cet) as begin_cet,
    sum(volume_buy_mw)  as buy_mw,
    sum(volume_sell_mw) as sell_mw
from fct_volume_pwr_15m
where delivery_date_utc between '2026-10-25' and '2026-10-26'
group by 1, 2
order by 1, 2

MIN(begin_cet) is safe because the (delivery_date_utc, begin_utc) pair uniquely identifies a period — begin_cet is functionally dependent on begin_utc.

Group by hour — use UTC

select
    date_trunc('hour', begin_utc) as hour_utc,
    sum(volume_buy_mw)             as buy_mw
from fct_volume_pwr_15m
where delivery_date_utc between '2026-10-25' and '2026-10-26'
group by 1
order by 1

Grouping by date_trunc('hour', begin_cet) instead would merge the two 02:00 CET hours on fall-back day into a single 8-row bucket.

Group by date — use delivery_date_utc

delivery_date_utc is the UTC calendar date, so it bins consistently. Don't substitute date_trunc('day', begin_cet) — on the DST boundary days that bins by local date and the local day can be 23 or 25 hours long.

When CET is the right answer

For tariff or market-window analysis that's defined in local time (e.g. "peak hours 08:00–20:00 CET on weekdays"), filter on begin_cet directly:

select
    delivery_date_utc,
    sum(volume_buy_mw) as peak_buy_mw
from fct_volume_pwr_15m
where delivery_date_utc between '2026-04-01' and '2026-04-30'
  and dayofweekiso(begin_cet) between 1 and 5
  and hour(begin_cet) between 8 and 19
group by 1

Be aware that on DST boundary days the count of in-window periods will be 92 or 100 instead of 96 × (window fraction).

Display formatting

Snowflake's to_varchar honours the TIMESTAMP_TZ time zone for display. So:

select
    to_varchar(begin_cet, 'YYYY-MM-DD HH24:MI TZHTZM') as local_label
from fct_volume_pwr_15m
where delivery_date_utc = '2026-03-29'
order by begin_utc

…produces strings like 2026-03-29 01:45 +0100 and 2026-03-29 03:00 +0200 — making the spring-forward jump visible.