14 min read 3062 words

Aligning Timezones and DST in Hourly Generation Timeseries

The scenario: modelled and metered hourly output are joined for a validation study, the correlation is 0.98 for most of the year and collapses for two days in March and November, and the annual totals differ by 0.01 percent. One series is in local time with daylight saving, the other in UTC, and the join silently dropped one hour in spring and doubled one in autumn. This page makes those two days behave, and it extends temporal data aggregation.

Root-cause analysis

Three properties of local time break an hourly join, and all three are invisible in an annual total.

  1. A spring-forward day has 23 hours. The local clock jumps from 01:59 to 03:00, so an index built by adding one hour at a time in local time either contains a timestamp that does not exist or is one hour short. A join on that index drops the corresponding row from the other series.
  2. A fall-back day has 25 hours. 01:00 to 01:59 occurs twice with different UTC offsets. A naive index has two rows with the same label, so a join produces a Cartesian product for that hour and an aggregation double-counts it.
  3. A mixed convention within one dataset. SCADA exports frequently switch between local standard time and local clock time between vintages, and the switch is silent because both look like local time. The symptom is a one-hour offset that appears partway through a year.
Spring forward and fall back, hour by hour Two hourly timelines for a single day each. The spring-forward day shows local hours 00:00 and 01:00 followed directly by 03:00, with a gap where 02:00 would be, marked as a nonexistent timestamp and a 23-hour day. The fall-back day shows 00:00, then 01:00 twice — once at the summer offset and once at the standard offset — then 02:00, marked as an ambiguous label and a 25-hour day. Beneath each, the failure it causes in a join: a dropped row and a duplicated match respectively. Two days a year, and both break an hourly join spring forward · 23 hours 00:00 01:00 does not exist 03:00 04:00 05:00 a modelled row labelled 02:00 matches nothing — the join drops it fall back · 25 hours 00:00 01:00 01:00 again (EST) 02:00 03:00 04:00 the label 01:00 matches twice — the aggregation counts it twice Both are single hours in 8 760, so the annual total moves by hundredths of a percent — which is why the defect is usually found by an hourly correlation rather than by a total.

Pre-flight validation

An hourly index is complete or it is not, and checking is three lines. Do it before any join, on both sides.

python
import pandas as pd


def audit_hourly_index(index: pd.DatetimeIndex, *, year: int) -> dict:
    """Completeness, duplication and timezone awareness of an hourly index."""
    expected = 8784 if pd.Timestamp(year=year, month=1, day=1).is_leap_year else 8760
    tz = index.tz
    report = {
        "tz": str(tz) if tz is not None else "naive",
        "rows": len(index),
        "expected_utc_hours": expected,
        "duplicates": int(index.duplicated().sum()),
        "monotonic": bool(index.is_monotonic_increasing),
        "gaps": 0,
    }
    if tz is not None:
        utc = index.tz_convert("UTC")
        full = pd.date_range(utc.min(), utc.max(), freq="h", tz="UTC")
        report["gaps"] = int(len(full) - len(utc.unique()))
    else:
        full = pd.date_range(index.min(), index.max(), freq="h")
        report["gaps"] = int(len(full) - len(index.unique()))

    report["complete"] = (
        report["duplicates"] == 0 and report["gaps"] == 0 and report["rows"] == expected
    )
    return report

A naive index reporting 8,760 rows is not evidence of correctness — a local-time year with a dropped spring hour and a duplicated autumn hour also totals 8,760. The duplicate and gap counts are what distinguish them.

Fix implementation

The rule that removes the whole class of problem: UTC everywhere inside the pipeline, a fixed offset where local time is genuinely required, and DST-aware zones only at presentation.

python
import pandas as pd


def to_utc_working_index(
    df: pd.DataFrame,
    *,
    source_tz: str | int,
    ambiguous: str = "raise",
    nonexistent: str = "raise",
) -> pd.DataFrame:
    """Normalise any incoming series to a UTC index, failing loudly on the two bad days.

    source_tz may be an IANA name for clock time, or an integer offset in hours for
    local standard time — which is what most SCADA and resource files actually use.
    """
    out = df.copy()
    idx = pd.DatetimeIndex(out.index)

    if isinstance(source_tz, int):
        # Fixed offset: no DST, no ambiguity, no missing hour. The preferred input.
        out.index = idx.tz_localize(f"Etc/GMT{-source_tz:+d}").tz_convert("UTC")
        return out

    if idx.tz is None:
        # Clock time: the two transition days need an explicit policy.
        out.index = idx.tz_localize(source_tz, ambiguous=ambiguous, nonexistent=nonexistent)
    out.index = out.index.tz_convert("UTC")
    return out


def join_modelled_and_metered(
    modelled: pd.DataFrame,
    metered: pd.DataFrame,
    *,
    modelled_tz: str | int,
    metered_tz: str | int,
) -> pd.DataFrame:
    """Join two hourly series that arrived in different conventions."""
    m = to_utc_working_index(modelled, source_tz=modelled_tz)
    g = to_utc_working_index(metered, source_tz=metered_tz)

    joined = m.join(g, how="outer", lsuffix="_model", rsuffix="_meter")
    missing_model = int(joined.filter(like="_model").isna().all(axis=1).sum())
    missing_meter = int(joined.filter(like="_meter").isna().all(axis=1).sum())
    joined.attrs["join_report"] = {
        "rows": len(joined),
        "hours_only_in_modelled": missing_meter,
        "hours_only_in_metered": missing_model,
        "overlap_hours": len(joined) - missing_model - missing_meter,
    }
    return joined

Passing ambiguous="raise" and nonexistent="raise" rather than the convenient "NaT" is the decision that matters. A pipeline that silently drops the nonexistent hour and picks one of the two ambiguous ones produces a plausible series; one that raises tells you the source convention was not what you assumed.

UTC, fixed offset and DST-aware zones compared A three-row comparison of time conventions. UTC: 8,760 hours in a common year, no duplicated labels, no missing labels, safe for storage, joining and aggregation. Fixed local standard offset such as Etc/GMT plus six: the same hour count and the same guarantees, reads as local time, and is what most resource and SCADA exports actually contain. Daylight-saving-aware zone such as America slash New York: 8,759 hours in spring-forward years and 8,761 in fall-back terms, one duplicated label and one nonexistent label, safe only for presentation. Store in UTC, render in local — the middle row is why convention hours duplicated missing safe for UTC 8 760 none none storage · joins · aggregation fixed offset (Etc/GMT+6) 8 760 none none resource and SCADA files DST-aware (America/New_York) 8 759 / 8 761 one label twice one label missing presentation only Most resource and SCADA files are the middle row and are labelled as though they were the bottom one — which is why the source convention belongs in the file metadata rather than in an assumption.

Fallback routing and performance tuning

  • Store UTC, render local. Every artefact in the store carries a UTC index; presentation applies a zone at the last moment. This makes every join, aggregation and hour count exact by construction.
  • Prefer a fixed offset for resource data. NSRDB, ERA5 and most SCADA exports are local standard time or UTC, never clock time, so Etc/GMT+6 is both correct and immune to DST entirely.
  • Never concatenate local-time years. The repeated autumn hour appears twice across the boundary and the missing spring hour once, so a multi-year concatenation in local time is wrong at every transition.
  • Resample in UTC. A daily or monthly reduction over a DST-aware index produces days of 23 and 25 hours, which is correct for a clock-time question and wrong for an energy one.
  • Record the source convention per file. It is the field that is most often assumed and least often written down, and a mixed-convention dataset is only detectable if the convention is stated.

Downstream validation

python
import pandas as pd


def assert_hourly_join_complete(joined: pd.DataFrame, *, year: int, min_overlap: float = 0.99) -> None:
    """The join must cover the year exactly once, with no duplicated or missing hours."""
    report = joined.attrs.get("join_report", {})
    expected = 8784 if pd.Timestamp(year=year, month=1, day=1).is_leap_year else 8760

    assert joined.index.tz is not None, "the joined index is timezone-naive — convention unknown"
    assert str(joined.index.tz) == "UTC", f"joined index is in {joined.index.tz}, not UTC"
    assert not joined.index.duplicated().any(), "duplicate timestamps — a fall-back hour survived"
    assert len(joined) == expected, f"{len(joined)} hours in the join, expected {expected}"

    overlap = report.get("overlap_hours", 0) / max(len(joined), 1)
    assert overlap >= min_overlap, (
        f"only {overlap:.1%} of hours appear in both series — the two conventions disagree"
    )

The two days, concretely

Four index checks, and the failure each one catches A four-row table pairing an index check with the failure it catches. Row count equal to 8,760 or 8,784 catches a truncated or extended series. No duplicated timestamps catches a surviving fall-back hour that would match twice in a join. No gaps against a continuous UTC range catches a dropped spring-forward hour. A timezone-aware index catches a naive one, which carries no convention and cannot be safely converted. A note records that a naive local-time year can total exactly 8,760 rows while containing both a duplicate and a gap. A row count of 8 760 is not evidence of correctness len(index) == 8 760 or 8 784 a truncated or extended series no duplicated timestamps a surviving fall-back hour no gaps against a UTC range a dropped spring-forward hour index.tz is not None a naive index with no convention A naive local-time year with one hour dropped in spring and one duplicated in autumn totals exactly 8 760 rows — which is why the duplicate and gap checks matter more than the count.

It helps to look at exactly what happens on each transition, because the failures are specific rather than general.

Spring forward. In US Eastern time, 2026-03-08 runs 00:00, 01:00, then 03:00 — 02:00 to 02:59 does not exist. A modelled series generated by adding one hour at a time in local time will contain a 02:00 that no metered record can match, and tz_localize with nonexistent="raise" will say so. With nonexistent="shift_forward" the row silently becomes 03:00 and collides with the real 03:00, which is how a duplicate appears in a series that started with none.

Fall back. On 2026-11-01 the same zone runs 00:00, 01:00 (EDT), 01:00 (EST), 02:00 — the label 01:00 occurs twice with different UTC offsets. Joining on the label produces two matches for one modelled hour, and summing produces 25 hours of generation in a 24-hour day. ambiguous="raise" catches it; ambiguous=True picks the first occurrence and quietly discards the second hour of real generation.

Both are single hours in 8,760, which is why the annual total moves by hundredths of a percent and nobody notices until an hourly comparison is attempted. The correlation collapse in the opening scenario is the same defect seen from a different angle: two series offset by an hour for part of the year correlate poorly on exactly those days and well everywhere else.

Frequently asked questions

Is Etc/GMT+6 really six hours behind UTC?

Yes, despite the sign looking backwards. The Etc/GMT zones follow the POSIX convention where the sign is inverted, so Etc/GMT+6 is UTC−6 — US Central Standard Time. It is worth the confusion because these zones never observe DST, which is exactly the property a fixed-offset resource file needs.

What if the source does not say which convention it uses?

Infer it from the data and then confirm. A solar series in local standard time peaks near 12:00 local year-round; one in clock time peaks near 13:00 in summer; one in UTC peaks at an offset equal to the longitude. Plotting the mean diurnal profile by month makes the convention obvious in seconds, and the inference belongs in the file’s metadata once made.

Should the pipeline ever store local time?

Only as a derived column for presentation, never as the index. A local-time index makes every join and every hour count conditional on a zone, and the cost of converting at render time is nil.

How do leap seconds affect this?

They do not, in practice. Pandas and NumPy timestamps ignore leap seconds, meter data is not timestamped to that precision, and an hourly energy series has no way to represent one. It is the one timekeeping subtlety that can safely be ignored here.

What about half-hourly or five-minute data?

The same rules apply and the transitions get proportionally more interesting — a fall-back hour contains two of every sub-hourly interval. The completeness check generalises by replacing the expected hour count with the expected interval count, and the fixed-offset advice becomes more valuable rather than less.

How do I validate a fix?

Count. After conversion, an index should have exactly 8,760 or 8,784 unique UTC hours, no duplicates and no gaps, and the mean diurnal profile should place solar noon within a few minutes of the astronomical value for the site longitude. Those two checks together catch every failure described on this page.

Related articles

Back to Temporal Data Aggregation