Two Events Share a Local Time. Keep Both in pandas

Imagine two distinct events, E1 and E2, that both report the same local clock time “02:30:00” on October 26, 2025, the day Europe’s clocks fall back from DST. The question is not whether pandas can parse them into a datetime column (it can). It’s whether both events survive a downstream deduplication when timezone info is stripped.

In our fixed example, E0 occurs at 2025-10-26 01:30:00+02:00 (UTC 2025-10-25 23:30:00Z), E1 at 2025-10-26 02:30:00+02:00 (UTC 2025-10-26 00:30:00Z), and E2 at 2025-10-26 02:30:00+01:00 (UTC 2025-10-26 01:30:00Z). Both E1 and E2 display as “02:30:00” in Europe/Berlin time, but they are 60 minutes apart in reality.

We also include a duplicate of E1 (same ID, same timestamp) to model a permissible redelivery. By contract we should drop only the exact E1 duplicate and retain the three unique events (E0, E1, E2). If a naive timezone-strip/dedupe process removes E2 as a “duplicate” of E1, that is a failure.

We will trace event IDs through each transformation, distinguish true duplicates from repeated local times, and show how to quarantine or rebuild ambiguous cases. This playbook builds on pandas’ documented timezone localization and datetime parsing behavior and PEP 495’s DST fold semantics, and adds explicit tests and governance to preserve event identity through a DST repeated hour.

Define the Event Identity and Redelivery Contract

We label our four raw records with a business key “event_id,” a source timestamp string, and an offset. The fixture is (order shown; we will test order variations later):

import pandas as pd

# Raw offset-bearing input records
raw = pd.DataFrame([
    {"event_id": "E0", "src_timestamp": "2025-10-26T01:30:00+02:00"},
    {"event_id": "E1", "src_timestamp": "2025-10-26T02:30:00+02:00"},
    # exact redelivery
    {"event_id": "E1", "src_timestamp": "2025-10-26T02:30:00+02:00"},
    {"event_id": "E2", "src_timestamp": "2025-10-26T02:30:00+01:00"}
])

•   Event ID identifies the business event across systems.

•   Source instant: the true UTC time of the event, derived from the offset (our oracle). From the raw strings, we compute:

• E0 ⇒ UTC 2025-10-25 23:30:00Z

•  E1 ⇒ UTC 2025-10-26 00:30:00Z

•   E2 ⇒ UTC 2025-10-26 01:30:00Z

•   Displayed local clock: what we see when ignoring the offset (here “01:30:00” for E0, “02:30:00” for E1 and E2).

•   Offset: distinguishes instants when the local clock repeats.

•   Redelivery: the second E1 row is an exact duplicate (same ID and source timestamp); business rules say such exact duplicates may be deduplicated (e.g. drop the second). Do not drop E2: it has a different offset/instant despite the same “02:30:00” label.

In summary, we must preserve E0, E1, and E2 (three unique events) and remove only the extra E1 as a duplicate. (This mirrors principles in a typical Python data-cleaning workflow where accurate timestamp identity is crucial.)

Pin the pandas and Timezone-Data Environment

For reproducibility we specify our environment. We use Python 3.10 (full 64-bit CPython), pandas 2.3.3, and NumPy 1.25 on a Linux system with 64-bit datetime64-ns timestamps. Python’s zoneinfo (PEP 615) is our timezone backend, using the system IANA zone database (or the first-party tzdata) for Europe/Berlin. For example, on an Ubuntu-like system with tzdata 2025f, we verify:

import datetime, zoneinfo

berlin = zoneinfo.ZoneInfo("Europe/Berlin")
# Confirm DST-backward on 2025-10-26:
# 01:30 +02 (DST)
print(datetime.datetime(2025, 10, 26, 1, 30, tzinfo=berlin))
# 02:30 +01 (standard time)
print(datetime.datetime(2025, 10, 26, 2, 30, tzinfo=berlin))

Indeed, Europe/Berlin “falls back” at 03:00 CEST to 02:00 CET on Oct 26, 2025, so 02:30 occurs twice: once at UTC+2, then again at UTC+1 (as documented in the pandas example). We record these versions and timezone-data sources to ensure results are fixed. (By contrast, the roles of Python data-science libraries emphasize keeping core versions stable for reproducibility.)

Separate Runtime Versions From Timezone-Rule Versions

To avoid confusion, note that a pandas version alone doesn’t fix DST rules. We pin pandas 2.3.3 (last 2.x release), which assumes Python 3.8–3.11. The zone transitions come from the OS or tzdata version. For example, zoneinfo in Python 3.10 uses the underlying system’s tzdata (say 2025f) or falls back to the tzdata PyPI package. In our logs we document:

•   Python: 3.10.x, built on Linux (UTC timezone by default).

•   pandas/NumPy: 2.3.3, 1.25.x.

•   tzdata: IANA 2025f (system-provided).

•   datetime dtype: datetime64[ns].

•   Display resolution: nanoseconds.

•   Base locale: Europe/Berlin as specified.

Before running the acceptance test, we explicitly check that Europe/Berlin matches our expectations: on the fallback date 2025-10-26, 01:30 is +02 (CEST) and 02:30 repeats at +02 and +01. These settings remain constant; any change in tzdata or pandas would require revalidation.

Build the Offset-Aware Fixture and Independent Oracle

Now we create the in-memory DataFrame for the raw events and compute the “oracle” UTC times from the offsets (independent of any transformation function). In pandas:

# parse strings (yields tz-aware Timestamps)
raw["ts"] = pd.to_datetime(raw["src_timestamp"])
# convert each aware timestamp to UTC
raw["utc_instant"] = raw["ts"].dt.tz_convert("UTC")
print(raw[["event_id", "src_timestamp", "utc_instant"]])

This yields something like:

Notice the duplicate E1 rows have identical UTC instants (00:30Z) and ID. We treat one as an allowable redelivery (to be removed), not a separate event. The “oracle” instants (second column) confirm what each event should map to, without relying on the logic under test. (These come directly from the source offsets.) We keep this oracle data for later verification.

Reproduce the Timezone-Removal Failure

Next we simulate the flawed pipeline: parse the aware timestamps, convert to the target zone, then strip off the timezone, and finally drop duplicates by the naive clock. This is often done via:

df = raw.copy()
# Ensure datetime column (already tz-aware from parsing)
# align to local zone (no change here since offsets match)
df["local_time"] = df["ts"].dt.tz_convert("Europe/Berlin")
# remove tz, preserving wall clock
df["naive_time"] = df["local_time"].dt.tz_localize(None)
# Deduplicate on local clock time
dedup = df.drop_duplicates(subset=["naive_time"], keep="first")

Because tz_localize(None) removes the timezone while keeping the same clock time, the “naive_time” column will be:

Here E1 (redel) and E2 both appear as naively “2025-10-26 02:30:00”. Dropping duplicates by the naive_time keeps the first one (E1) and discards E2. Thus the flawed process would incorrectly lose event E2, thinking it was a duplicate of E1. In other words, this naive dedup treats distinct instants as the same event, simply because their wall-clock times matched.

This error arises because equal “02:30:00” wall-clock labels do not mean equal instants. In DST fall-back, a repeated hour creates exactly this situation: the two 02:30s occur at different UTC times. As PEP 495 explains, one needs the fold (or equivalent offset) to distinguish them. Here, stripping the timezone lost that information, so pandas treated them as duplicates.

Keep Display Equality Separate From Instant Equality

It is important to emphasize: E1 and E2 do have different UTC instants, even though they share a displayed “02:30”. For E1 the instant is 2025-10-26T00:30Z and for E2 it is 2025-10-26T01:30Z. But after tz_localize(None), both appear as “2025-10-26 02:30:00”. This mirrors the pandas example: “02:30:00 local time occurs both at 00:30:00 UTC and at 01:30:00 UTC” during a backward DST shift. In general, equal naive timestamps need not imply the same event. We must treat the wall-clock equality as ambiguous, not as duplicate evidence.

Compare Timezone Localization and Conversion

pandas offers two related but distinct operations on time zones. tz_localize attaches or removes a timezone on a datetime array without moving the clock time, whereas tz_convert shifts the timestamps to a new zone (changing the clock). In our case:

•   Series.dt.tz_localize(None) removes the timezone info while keeping the local times the same. For example, a datetime index 09:00+05:00 tz-localized to None becomes 09:00 (no +05 offset), not converted to 04:00 or similar. This is exactly what tz_localize(None) did above: it left “02:30” unchanged on the nose.

•   In contrast, Series.dt.tz_convert(None) first converts all times to UTC and then drops the timezone. For a timezone-aware timestamp, tz_convert(None) is akin to tz_convert("UTC") then tz_localize(None). For instance, in pandas 3.x docs: converting a Europe/Berlin 09:00+02 time to tz_convert(None) yields 07:00 (UTC).

To illustrate with our example: if we started from an aware 2025-10-26 02:30:00+02:00 (E1) and applied tz_localize(None), it stays “02:30:00”. But if we applied tz_convert(None), it would shift to “01:30:00” (UTC) before dropping the tz. Thus, simply swapping tz_localize for tz_convert (or vice versa) changes whether the timestamps are interpreted as wall time or actual instants. Importantly, neither operation alone enforces the business contract of deduplicating redeliveries: they just preserve or convert times. We note this to avoid the mistaken assumption that using one or the other “fixes” the problem by itself. It doesn’t.

Normalize Aware Inputs and Deduplicate by Key

A correct solution is to validate that each input has an offset, convert everything to UTC, and then deduplicate by the event key plus UTC instant. In code:

df = raw.copy()
# Ensure timezone awareness and convert to UTC instants:
df["instant"] = pd.to_datetime(df["src_timestamp"], utc=True)

With the utc=True option, pandas will return all timestamps as UTC-aware: it localizes naive inputs as UTC and converts aware inputs to UTC. In our case, the raw strings already have offsets, so pandas will convert them. We then have:

Now we drop exact duplicates by event_id and UTC instant:

df_unique = df.drop_duplicates(subset=["event_id","instant"], keep="first")

This keeps E0, E1, E2 (three rows). The second E1 is removed because it had the same key+instant as the first. All original instants are preserved (E1→00:30Z, E2→01:30Z). We thus meet the contract: only the exact redelivery is removed.

Reject Conflicting Reuse of an Event ID

If a row arrives with the same event_id but a different UTC instant, that is not a harmless redelivery; it is a data conflict. For example, if we had a fourth record {"event_id":"E1","src_timestamp":"2025-10-26T03:30:00+02:00"}, it would map to 2025-10-26T01:30:00Z, a different instant from E1’s 00:30Z. In this case:

conflicts = df_unique.groupby("event_id")["instant"].nunique()
if (conflicts > 1).any():
    # Handle conflict: e.g. quarantine all such IDs
    raise ValueError("Conflicting timestamps for event ID")

We would not silently choose one row or treat it as a duplicate. The conflict should be flagged (quarantined) because the same business ID has incompatible event times. This respects the intent: only exact ID+timestamp duplicates are deduped; anything else is an error to resolve explicitly.

Treat Naive Local Timestamps as a Separate Input Class

What if the source provides a naive local time with no offset at all? For instance:

naive_df = pd.DataFrame([
    {"event_id": "E3", "src_timestamp": "2025-10-26T02:30:00"}
])

Here we cannot determine which “02:30” this is (DST or post-DST). If we try to localize it:

try:
    pd.to_datetime(naive_df["src_timestamp"]).dt.tz_localize("Europe/Berlin")
except Exception as e:
    print(e)  # likely an AmbiguousTimeError

pandas will complain that 02:30 is ambiguous (it can’t infer fold without extra info). Even ambiguous="infer" would fail because there is no sequence to infer from. Therefore, a policy should quarantine or reject this row. We must not blindly assume, say, the first-occurring DST offset or use the server’s timezone; that is guessing. Without provenance (offset or other metadata), the event’s true instant is unknown. The safe approach is to flag the row as ambiguous. Downstream analysts can then decide how to get the correct offset (e.g. ask the source system).

Test Disambiguation Evidence by Changing Row Order

We must ensure that no hidden reliance on row order or pairing sneaks in. For example, some might try using ambiguous='infer' and assume a dataset’s order resolves DST. But if rows arrive shuffled or if one half of a pair is missing, inference breaks. Consider:

series = pd.Series(["2025-10-26 01:30:00", "2025-10-26 02:30:00"])
pd.to_datetime(series).dt.tz_localize("Europe/Berlin", ambiguous="infer")

If this series is sorted by time, pandas might infer that “02:30” is the DST instance (fold=0) and localize accordingly. But if we shuffle it:

series_shuffled = pd.Series([
    "2025-10-26 02:30:00", "2025-10-26 01:30:00"
])
pd.to_datetime(series_shuffled).dt.tz_localize(
    "Europe/Berlin", ambiguous="infer"
)

The result could change or raise, because the inference logic has no consistent ordering to use. Similarly, if one of the two 02:30 rows is missing, inference has no anchor. In general, sequence and missing-data should not be relied upon for DST. We explicitly test:

•   Sorted input vs reversed/shuffled input: the policy should yield the same classification (quarantine or resolved using explicit data), not vary with order.

•   Missing pair: if only one of the two “02:30” events is present (or their fold flags weren’t provided), we still must mark it ambiguous.

The key is: only trust offset or explicit metadata, not row order. In practice, we won’t use ambiguous='infer' at all. Instead, we treat any ambiguous case without full metadata as ambiguous (see the naive local timestamp policy above).

Require Provenance for an Explicit Ambiguity Choice

If the source does supply a DST flag (or if we have an independent “fold” indicator), then we could resolve ambiguous times. For example, if the source had provided a column like dst_flag=True/False for each row, we could use that boolean array with tz_localize(ambiguous=flag_array). But absent that, we make no assumptions. We do not accept a line as resolved just because it happened first in time, or use the machine’s timezone. In other words, only trust real provenance (offset or equivalent fold) to disambiguate.

Capture Identity at Every Transformation Boundary

We should be able to trace each row through the pipeline. For auditability, we produce a concise trace table. For our three accepted events (E0, E1, E2), we capture at least: event ID, raw source text, source offset, computed UTC instant, displayed local time, pandas dtype, and the admission decision. For example:

E1* denotes the redelivery duplicate. Note that despite E1 and E2 both displaying “02:30:00”, we kept them separate (different instants); only the marked duplicate was removed. The dtype and offset columns show exactly how pandas handled the data. This trace proves that event identities (IDs and instants) flowed correctly.

Rebuild From Preserved Raw Data Rather Than Guessing

Given this issue, a good policy is: never discard the original offset-bearing data in the pipeline. Instead of publishing a “cleaned” dataset where DST info is gone, keep the raw timestamps so we can always rebuild. If a transformation inadvertently dropped E2, we should go back to the raw DataFrame and regenerate the output correctly (as done above). Only if both a record and its offset evidence are lost is the situation irrecoverable, and in that case the pipeline should flag an error.

For instance, suppose a flawed script had dropped E2. We’d take the raw inputs and re-run:

rebuild = raw.copy()
rebuild["instant"] = pd.to_datetime(rebuild["src_timestamp"], utc=True)
rebuild = rebuild.drop_duplicates(subset=["event_id","instant"])

This rebuild will have E0, E1, E2. Before replacing the published output, we must compare it to the oracle of expected events. It’s not enough that the row count matches 3; we verify each event’s UTC instant matches expectation:

# Compare rebuilt vs oracle
for , row in rebuild.iterrows():
    expected = {
        "E0": "2025-10-25T23:30:00Z",
        "E1": "2025-10-26T00:30:00Z",
        "E2": "2025-10-26T01:30:00Z"
    }
    assert row["instant"] == pd.todatetime(expected[row["event_id"]])

If any row is missing or swapped (e.g. if E1 and E2 were mistakenly interchanged), this check would fail. We see that even if two timestamps ended up in the output, if the event IDs were mixed up, that’s still wrong. Thus we reconcile at the level of event ID and instant.

Add Regression Tests Around the Repeated Hour

We codify specific tests so this never regresses. At minimum, include these cases (each with expected outputs):

•   Normal distinct DST events: Input E0/E1/E2 as above. Accept 3 events; drop 1 duplicate.

•   Exact redelivery: Two identical rows (same ID+offset). Accept 1, drop the duplicate.

•   ID conflict: Same ID, different offset (e.g. E1 at +02 and E1 at +01 in one row set). Quarantine both or mark conflict (no accept).

•   Naive local time: A row like “2025-10-26 02:30:00” alone. Quarantine (no accept).

•   Shuffled order: Randomize the row order of a DST pair. Outcome should be unchanged (no additional accept or drop).

•   Missing pair: One 02:30 record without its counterpart. That single 02:30 with no offset should be quarantined as ambiguous.

Each test asserts the count of accepted events and, crucially, that the accepted events’ IDs map to the correct instants. (For example, if only one “02:30” row is accepted, it must match E1 or E2 correctly, not arbitrarily assume one.) These regression tests form a fence around the bug scenario.

Use an Explicit Accept, Refactor, Quarantine, and Rebuild Matrix

For maintainability, we define an explicit decision matrix. For each identified issue type, we specify the action, evidence needed, and accountability:

•   Exact duplicate (same ID and UTC): Accept one, drop others. Evidence: identical event_id and instant. Owner: Transformation code (keep-first).

•   Offset conflict (same ID, different instant): Quarantine. Evidence: event_id duplicated with ≥2 distinct instant values. Owner: Data steward or operator must resolve.

•   Ambiguous local (no offset): Quarantine. Evidence: timestamp string parsed as naive and falls in a DST fold. Owner: Data provider or annotation process.

•   Missing timestamp: Refactor. (For example, if a cleaning step erroneously removed an offset, fix the code.) Evidence: loss of offset info downstream. Owner: Data engineering (fix code or process).

•   Transformation environment change: Rebuild/Retest. If pandas or tzdata updates, rerun all checks to ensure nothing silently changed. Owner: Data engineering governance.

We also publish any “hold” conditions. For example, if rows are quarantined, downstream datasets should include a note or separate table indicating “1 row unresolved: E3 at 2025-10-26 02:30:00 (ambiguous)” rather than pretending the output is complete. This ensures users know whether they have a fully clean set or if some data is on hold.

Assign Temporal Ownership Across the Pipeline

Finally, data ownership: clearly assign responsibilities. The source system must supply reliable timestamps with timezone (or equivalent disambiguators). The transformation layer (our pandas code) must apply the policy we’ve defined (reject vs dedupe) and keep version records. Consumers (analysts) must know what policy was used. In a cloud-native data-engineering setting, this means versioning the transformation logic together with the data contract. Critically, whenever the timezone database changes (e.g. a new tzdata release alters historical offsets), the pipeline owner should re-run these tests, because DST rules can drift over years, and a previously unambiguous date might become ambiguous (or vice versa). At least, any change in environment requires revalidation of these assertions against the raw oracle.

Develop a Data-Science Practice That Preserves Meaning

This scenario underscores a key data-science principle: preserve raw meaning through transformations. Before dropping the timezone, we verified against an independent UTC “oracle” and recorded every transformation. Such rigor is consistent with reproducible analysis best practices. Learning to do this properly is exactly the kind of skill cultivated in a practical program. For example, Refonte’s Data Science & AI program (a 3-month course at ~12–14 hours/week) covers Python analytics with pandas and NumPy, along with guided project work and mentoring. By following these disciplined steps (coding defensively, writing thorough tests, and never losing original data), data practitioners can avoid subtle DST traps and build pipelines that analysts trust.