DST gap and overlap in pandas timestamp normalization

On the second Sunday of a fall-back night, a 25-hour local day arrives from the meter feed and pandas raises AmbiguousTimeError on the duplicated 01:00–02:00 hour — or worse, does not raise, silently keeps only one of the two readings, and the day’s imbalance under-settles by an hour of energy. Spring forward is the mirror failure: the 02:00–03:00 hour does not exist, NonExistentTimeError fires, and an interval that should shift to the next valid slot instead vanishes or collapses onto its neighbor. Both are the same root problem — normalizing wall-clock timestamps to a tz-aware axis without telling pandas exactly what to do at the two transitions each year. This guide fixes it deterministically. It sits under Pandas for Trade Data Processing within the Trade Ingestion & Matching Workflows framework, immediately after a feed has been parsed into a typed frame and before any interval is netted.

The flow below traces a naive local timestamp column through explicit ambiguous and nonexistent resolution into a UTC-anchored index whose interval count is provably 23, 24, or 25 hours as the calendar demands — never a silently collapsed or doubled day.

Prerequisites #

• Python 3.11+ with pandas>=2.1. The stdlib zoneinfo module supplies IANA zones (America/New_York, America/Chicago); no third-party tz package is required, though tzdata must be installed on minimal containers.
• The market’s canonical time zone, per feed. Settlement follows the ISO/RTO’s local prevailing time — PJM and ISO-NE settle in America/New_York, MISO largely in America/Chicago — so the zone is a property of the source, versioned alongside your ISO/RTO Data Format Standards baselines.
• Interval granularity — hourly or five-minute — since the expected interval count per day scales with it and the invariant check depends on knowing it.
• A quarantine path for readings that cannot be disambiguated, so a genuinely unresolvable timestamp is logged as evidence rather than dropped.

Implementation #

The whole problem lives in one method call, tz_localize, and its two keyword arguments. ambiguous tells pandas how to resolve the fall-back hour that occurs twice; nonexistent tells it what to do with the spring-forward hour that never occurs. Leave either at its default and pandas raises on the transition day, which in a batch means the entire day fails or, if someone wrapped it in a bare except, disappears.

Start with fall back. On that night the local clock runs 01:00, 01:30, then repeats 01:00, 01:30 before continuing to 02:00 — two distinct UTC instants share one wall-clock label. Meter feeds that carry a DST flag let you disambiguate precisely; feeds that do not force a documented convention.

from zoneinfo import ZoneInfo
import numpy as np
import pandas as pd

MARKET_ZONE = "America/New_York"  # PJM / ISO-NE prevailing time

def localize_fall_back(local_ts: pd.Series, is_dst_flag: pd.Series | None = None) -> pd.Series:
    """Attach the market zone to naive wall-clock timestamps, resolving the
    duplicated fall-back hour explicitly rather than letting pandas raise."""
    if is_dst_flag is not None:
        # Feed carries the offset: True = first (still-DST) pass, False = second (standard) pass.
        ambiguous = is_dst_flag.to_numpy(dtype=bool)
    else:
        # No flag: infer the true order from the monotonically increasing raw feed.
        # 'infer' assumes readings arrive in real chronological sequence.
        ambiguous = "infer"

    localized = local_ts.dt.tz_localize(
        MARKET_ZONE,
        ambiguous=ambiguous,
        nonexistent="raise",   # fall-back path should never hit a gap; fail loud if it does
    )
    return localized

Spring forward is the opposite defect: 02:00–02:59 local does not exist, so a meter that stamps 02:30 is describing a wall-clock reading the clock skipped. The correct settlement behavior is to move that reading to the first valid instant — shift_forward maps it to 03:00 rather than deleting it, preserving the interval’s energy.

def localize_spring_forward(local_ts: pd.Series) -> pd.Series:
    """Localize across the spring-forward gap, shifting the nonexistent hour
    forward to the next valid instant so no interval is silently dropped."""
    localized = local_ts.dt.tz_localize(
        MARKET_ZONE,
        ambiguous="raise",          # spring path should never hit a duplicate; fail loud
        nonexistent="shift_forward" # 02:30 -> 03:00, energy preserved
    )
    return localized

In production you rarely know in advance which transition a given day sits on, so wrap both rules into one normalizer that applies the safe convention for each direction and quarantines anything genuinely unresolvable. Anchoring everything to UTC immediately after localization gives a single monotonic axis where the fall-back hour is two distinct instants and the spring gap simply is not there — which is exactly what netting needs.

import logging

logger = logging.getLogger("dst_normalize")

def normalize_to_utc(df: pd.DataFrame, ts_col: str = "interval_start") -> tuple[pd.DataFrame, pd.DataFrame]:
    naive = pd.to_datetime(df[ts_col], errors="coerce")
    flag = df["is_dst"] if "is_dst" in df.columns else None

    try:
        localized = localize_fall_back(naive, flag)
    except pd.errors.AmbiguousTimeError:
        # Duplicate hour with no usable flag and non-monotonic feed — cannot resolve safely.
        logger.error("Unresolvable fall-back ambiguity in %s; quarantining transition rows", ts_col)
        localized = naive.dt.tz_localize(
            MARKET_ZONE, ambiguous="NaT", nonexistent="shift_forward"
        )

    # For spring days the fall-back call already used nonexistent handling; ensure gap safety
    if localized.isna().any():
        # Retry the un-localized rows through the spring-forward rule
        gap_mask = localized.isna() & naive.notna()
        localized.loc[gap_mask] = naive[gap_mask].dt.tz_localize(
            MARKET_ZONE, ambiguous="NaT", nonexistent="shift_forward"
        )

    df = df.assign(interval_utc=localized.dt.tz_convert("UTC"))
    unresolved = df[df["interval_utc"].isna()].assign(reject_reason="dst_unresolvable_timestamp")
    clean = df[df["interval_utc"].notna()].copy()
    logger.info("Normalized %d intervals; quarantined %d unresolvable", len(clean), len(unresolved))
    return clean, unresolved

Note that no money arithmetic happens in this stage — that is deliberate. Timestamp normalization runs before netting, so when charges are computed downstream they operate on a correct 23-, 24-, or 25-hour axis, and the Decimal money math in the Async Batch Processing Pipelines that follow can trust every interval is counted exactly once. The distinct behavior of the two transitions is worth keeping in front of you:

Verification #

The proof that DST was handled correctly is an interval-count invariant: the number of localized intervals for a settlement day must equal the calendar-correct count for its length. For hourly data that is 23 on spring-forward, 24 on a normal day, and 25 on fall-back; for five-minute data, multiply by twelve.

$$n_{\text{intervals}} = \frac{\big(t_{\text{end}}^{,\text{UTC}} - t_{\text{start}}^{,\text{UTC}}\big)}{\Delta_{\text{interval}}}$$

from decimal import Decimal

def verify_dst_day(clean: pd.DataFrame, trade_date, interval_minutes: int = 60) -> None:
    day = clean[clean["interval_utc"].dt.tz_convert(MARKET_ZONE).dt.date == trade_date]
    utc = day["interval_utc"].sort_values()
    span_hours = (utc.iloc[-1] - utc.iloc[0]).total_seconds() / 3600 + interval_minutes / 60
    expected = {23, 24, 25} if interval_minutes == 60 else {276, 288, 300}

    assert len(day) in expected, f"{trade_date}: got {len(day)} intervals, expected one of {expected}"
    # No duplicate UTC instants — the fall-back hour must be two DISTINCT timestamps
    assert utc.is_unique, f"{trade_date}: duplicated UTC instant — fall-back hour collapsed"
    # Span must match count exactly — a dropped spring-forward interval shows up here
    assert round(span_hours) in {23, 24, 25}, f"{trade_date}: span {span_hours}h inconsistent with intervals"

Run three concrete checks on every batch that crosses a transition date: the count assertion above; a monotonic-and-unique check on the UTC index, which catches a collapsed fall-back hour that a count alone would miss; and a total-energy reconciliation confirming the summed MWh over the localized day equals the summed MWh over the raw feed, so no interval’s quantity was dropped or double-counted. If any check fails, halt and route the day to quarantine rather than settling a mis-counted day. Represent the reconciled energy total as Decimal when you compare it, so a 25-hour fall-back day does not appear balanced through floating-point rounding.

Compliance Note #

FERC and NERC settlement rules require that every metered interval be settled once and only once against the correct hour, and the two DST transitions are the classic way that requirement breaks. A collapsed fall-back hour under-reports an hour of energy; a dropped spring-forward interval leaves a gap that resettlement will later have to chase. Two controls satisfy the audit surface. First, make the disambiguation rule explicit and documented — record whether the feed’s DST flag or the "infer" convention resolved each transition, keyed by feed and trade date, so the choice is reproducible rather than incidental to a pandas default. Second, store the interval-count invariant result as a settlement-day attestation: the assertion that the day held its calendar-correct 23, 24, or 25 hours is the evidence an auditor needs that no interval collapsed or doubled. Anchoring to UTC and only ever converting back to prevailing time for display keeps the netting axis unambiguous, which is the same discipline the Trade Ingestion & Matching Workflows framework applies to every timestamp before it reaches the ledger. For the underlying semantics, the pandas time series documentation specifies exactly how ambiguous and nonexistent behave.

Frequently Asked Questions #

Why does tz_localize raise AmbiguousTimeError on fall-back nights? #

Because the wall-clock hour from 01:00 to 01:59 occurs twice on that night — once still on daylight time and once on standard time — so a single naive timestamp maps to two possible UTC instants and pandas refuses to guess. Resolve it explicitly: pass the feed’s DST offset flag to ambiguous, or use ambiguous="infer" when the raw readings arrive in true chronological order. Never wrap the call in a bare except that drops the transition rows, because that is precisely how an hour of energy goes missing from settlement.

What should happen to a timestamp inside the spring-forward gap? #

The 02:00–02:59 local hour does not exist, so a reading stamped there describes an instant the clock skipped. The settlement-correct behavior is to move it to the first valid instant with nonexistent="shift_forward", which maps 02:30 to 03:00 and preserves the interval’s energy. Deleting the reading or letting NonExistentTimeError abort the batch both lose data; shifting forward keeps the interval count and the metered quantity intact.

Should settlement timestamps be stored in local time or UTC? #

Store and compute in UTC, and convert back to the market’s prevailing time only for display or regulatory reports. On a UTC axis the fall-back hour is two distinct, monotonically increasing instants and the spring-forward gap simply does not exist, so netting and joins can never misalign an interval. Keeping local time as the working axis reintroduces the ambiguity at every downstream step; anchor once to UTC immediately after localization and the rest of the pipeline stays unambiguous.

Related #

• Pandas vs Polars for settlement batch processing
• Parsing CSV vs XML trade feeds with pandas
• Pandas for Trade Data Processing