When the Clock Repeats: Audit pandas Timestamps Before Aggregation

A small synthetic sales report illustrates the problem: after correcting the parsing of timestamps, the count of events and daily totals can shift unexpectedly. For example, a local timestamp of 1:30 AM appears twice around a daylight-saving fall-back change; choosing one occurrence changes the November 1 totals in Los Angeles. A successful parse does not guarantee the correct instant. In this scenario, we audit each row's timestamp evidence before grouping, carrying unresolved ambiguity forward instead of silently dropping it. Our pipeline uses a six-row fixture set in America/Los_Angeles around the 2020 DST transitions. Accepted rows receive a definite UTC instant and a reporting-zone date, while others remain quarantined with reason codes. We conserve all event IDs and amounts in a temporal-evidence ledger. Every input row remains in the reconciliation. Unresolved source evidence is held for review rather than forced into a possibly wrong instant.

Define what each clock value is supposed to mean

Before any code, it helps to distinguish key “time” fields and who owns them:

Each column is a distinct concept. This article is not a migration tutorial on zoneinfo versus pytz; see the existing pandas 3 migration boundaries for those API changes. Here, we audit timestamp provenance instead. We list the raw time as given, track any declared zone or offset, and only then assign a canonical instant. Finally, we derive the reporting date explicitly. The table clarifies how each field is meant to be used. The pandas time-series documentation and DatetimeIndex.tz_localize reference explain that ambiguous and nonexistent local times require explicit handling, but they do not tell us which occurrence or corrected time the source event actually meant. Our focus is on preserving evidence: the raw text and any supplied offset belong to the source, while the converted UTC instant and report date belong to the pipeline. This separation makes later corrections traceable.

Preserve source evidence before parsing

We start by fixing an input contract that carries everything we need (and nothing we don’t). Each event row must include:

•         event_id: Unique identifier from the source (never generated or changed by us).

•         raw_timestamp: The original timestamp string (immutable copy from source).

•         source_zone or source_offset: Any time-zone info or UTC offset the source provided. If missing, leave null.

•         amount_minor (int): The event’s quantity or sales amount in “minor units.”

•         ingest_metadata: Optional fields (e.g. ingestion time, batch ID) for auditing, not used in time logic.

For example, our fixture's columns will be exactly these. We never overwrite raw_timestamp with a parsed datetime or arbitrarily attach UTC unless the source contract justifies it. Nor do we drop the original text if parsing fails. This practice reflects data lineage and analytics validation responsibilities: input fields remain source evidence. An example checklist follows.

•         event_id: from upstream system (kept exact).

•         raw_timestamp: copied as-is (string).

•         source_zone/offset: either from source field (e.g. “America/Los_Angeles”) or blank.

•         amount_minor: as given (converted to integer).

•        ingest_time: recorded by our pipeline (timestamp of load).

All parsing decisions must be traceable back to these. For instance, if we later quarantine a row, the reason code will reference the fact that source_zone was null or that two events had the same raw_timestamp. By preserving each input field, we ensure an auditor can replay or challenge any decision. This contrasts with a brute-force approach that, say, immediately converts all strings with utc=True and loses the original context. In summary, keep every source value immutable and mark which are from the source versus which are policy-induced.

Build a fixture with repeated and missing local times

We illustrate with a six-row synthetic fixture spanning the 2020 DST transitions in America/Los_Angeles. The event IDs are distinct and static. This ensures our audit focuses only on how we interpret the times, not on identity. The rows include:

# Synthetic event fixture (6 rows):
# Columns: event_id, raw_timestamp, source_zone, amount_minor
data = [
    {"event_id": "A", "raw_timestamp": "2020-11-01T01:30:00-07:00", "source_zone": None, "amount_minor": 1200},
    {"event_id": "B", "raw_timestamp": "2020-11-01T01:30:00-08:00", "source_zone": None, "amount_minor": 1800},
    {"event_id": "C", "raw_timestamp": "2020-11-01 01:30:00",        "source_zone": "America/Los_Angeles", "amount_minor": 2500},
    {"event_id": "D", "raw_timestamp": "2020-03-08 02:30:00",        "source_zone": "America/Los_Angeles", "amount_minor": 700},
    {"event_id": "E", "raw_timestamp": "2020-11-01T06:30:00Z",        "source_zone": None, "amount_minor": 900},
    {"event_id": "F", "raw_timestamp": "2020-11-01T08:30:00Z",        "source_zone": None, "amount_minor": 600},
]

Notice the setup:

•         Rows A and B: Both have raw timestamps of “2020-11-01 01:30” but with explicit offsets -07:00 (A) and -08:00 (B). These represent the two occurrences of the 1:30 AM wall clock when DST ended on Nov 1, 2020. They must remain distinct events, because they map to different instants.

•         Row C: Has the same wall time “2020-11-01 01:30:00” without any offset, only a timezone label (“America/Los_Angeles”). This is an ambiguous case: without more evidence, we don’t know if it was in PDT or PST. Our policy will quarantine it until more information is available (or business rules apply).

•         Row D: Occurs at “2020-03-08 02:30:00” in the Los Angeles zone. On that date, clocks jumped from 1:59 to 3:00, so 2:30 AM never happened. This row is a nonexistent time. We also will hold it for review rather than arbitrarily shifting it.

•         Rows E and F: Their timestamps are explicit UTC values marked with Z. E converts to 2020-10-31 23:30 PDT in Los Angeles, while F converts to 2020-11-01 01:30 PDT. E therefore tests a report-date boundary even though both UTC dates are November 1.

Under our proposed policy, events A, B, E, and F are accepted because each has a clear instant. Events C and D are quarantined because C lacks occurrence evidence for the repeated hour and D names a nonexistent local time. All six input rows and 7,700 minor units reconcile to four accepted rows and 4,500 units plus two quarantined rows and 3,200 units. Accepted local-day totals are 900 on October 31 and 3,600 on November 1. All accepted UTC dates are November 1. These are expected arithmetic results from the synthetic fixture; a pinned executable lab must verify them before they are presented as observed output.

Represent both occurrences of the repeated hour

To emphasize, we include two explicit events at the same local clock time. In code we might create them as pandas-aware Timestamps:

import pandas as pd
ts_A = pd.Timestamp("2020-11-01T01:30:00-07:00")  # first occurrence (PDT)
ts_B = pd.Timestamp("2020-11-01T01:30:00-08:00")  # second occurrence (PST)

Each yields a distinct UTC instant:

·        A: 2020-11-01 08:30:00+00:00 (because 1:30 PDT = UTC-7).

·        B: 2020-11-01 09:30:00+00:00 (because 1:30 PST = UTC-8).

In contrast, the naive event C with “2020-11-01 01:30:00” and zone “America/Los_Angeles” could map to either one. Without an offset, one might be tempted to use tz_localize("America/Los_Angeles"). But that call would raise or assume something (see below). Rather than guess, we will quarantine C. This way, A and B remain clearly separate events with preserved IDs and amounts. In other words, never collapse two known distinct events just because their wall-clock times coincide. We defer the decision for C to whoever has source insights (e.g. log order or user input). If we had evidence such as event ID increments or timestamps in another system, we could potentially infer that C is either the first or second 1:30. Without that, keeping C on hold retains data integrity.

Include a nonexistent wall time and a reporting boundary

Our fixture also includes event D, at the nonexistent local time “2020-03-08 02:30:00” in America/Los_Angeles. No instant corresponds to that wall-clock, since clocks jumped to 3:00 AM. Pandas will not magically create a valid moment for 2:30. We treat D as unparsed evidence of a gap. We might demonstrate in code that:

pd.to_datetime("2020-03-08 02:30:00").tz_localize("America/Los_Angeles")

The call raises by default because the wall time is nonexistent. The DatetimeIndex.tz_localize reference documents alternatives such as nonexistent='shift_forward', which moves the value to the first valid local time. That mechanism does not prove what instant the source intended. Under this audit policy, D remains unresolved and requires review.

Finally, event E at 2020-11-01T06:30Z (UTC) is included because it falls on Oct 31 in local time (PDT). This checks that we separate "event time" from "reporting date." Without careful logic, one might group events by UTC date and miss that E belongs in the Oct 31 local report. We will explicitly derive the report date in the intended zone later.

Choose parsing, localization and conversion deliberately

With the fixture defined, we parse each row in a controlled way. The key is to treat offset-aware and naive input according to their evidence and to avoid hidden UTC assumptions. The reviewed pandas documentation displayed version 3.0.6, but that is a documentation baseline rather than an executed runtime claim. Any executable lab must record the exact pandas patch, Python version, and time-zone database source and version. The examples below are conceptual and nonproduction.

import pandas as pd

# Example 1: already-offset timestamps
pd.to_datetime("2020-11-01T01:30:00-07:00", utc=True)
# This returns 2020-11-01 08:30:00+00:00, as expected.

pd.to_datetime("2020-11-01T01:30:00-08:00", utc=True)
# Returns 2020-11-01 09:30:00+00:00.

The pandas.to_datetime documentation explains how utc=True normalizes aware inputs to UTC and localizes naive inputs as UTC. Because A and B contain explicit offsets, utc=True preserves their instants while normalizing their representation. Without utc=True, an offset-bearing scalar remains timezone-aware with its fixed offset. The important point is that A and B are unambiguous because their source strings include the offsets; the parser did not discover them.

Next, naive strings:

# Naive local timestamps:
ts_naive = pd.to_datetime("2020-11-01 01:30:00")  # naive Timestamp

This ts_naive is a Timestamp with no tz (its tzinfo is None). It is not inherently UTC or LA; it is just a date-time. To interpret it, we must apply tz_localize or tz_convert appropriately:

•         If we localize a naive value with a known zone, pandas attempts to assign that zone to the wall-clock label. The DatetimeIndex.tz_localize reference documents ambiguous and nonexistent handling. By default, C raises for ambiguity and D raises for nonexistence. We do not override those outcomes without source evidence or an approved policy.

•         tz_convert requires an already-aware timestamp. Localizing a naive value to UTC merely to enable conversion would invent the missing source zone. For example, ts_naive.tz_localize('UTC').tz_convert('America/Los_Angeles') would reinterpret the source label as 2020-11-01 01:30 UTC and display it as 2020-10-31 18:30 in Los Angeles. That is not justified for C.

Contrast with pd.to_datetime(..., utc=True): for naive input, this is an assumption that the source wall-clock label is UTC. Applied to C, 2020-11-01 01:30 UTC displays as 2020-10-31 18:30 PDT in Los Angeles, not as either occurrence of 01:30 local time. We therefore use utc=True on naive strings only when the source contract explicitly specifies UTC. In this fixture, only E and F end with Z.

Finally, note how to remove timezone if needed. Pandas docs explain:

The tz_localize and tz_convert APIs remove zone information differently. tz_localize(None) drops the zone while preserving the displayed wall-clock values. tz_convert(None) first converts the aware values to UTC and then removes the zone. Either operation can erase context, so the release ledger must record why it was used.

For example:

dti = pd.date_range("2014-08-01 09:00", periods=3, tz="US/Eastern")
dti.tz_localize(None)  # ['2014-08-01 09:00', ...] (still 9AM Eastern times, no tz)
dti.tz_convert(None)   # ['2014-08-01 13:00', ...] (converted to UTC 13:00, then drop tz)

We will never call tz_localize(None) on our data unless explicitly removing a zone for display, because it could hide information. Likewise, tz_convert(None) we use only with awareness of that conversion step. In this audit, any loss of zone info must be noted in the ledger.

Contrast tz_localize, tz_convert and utc=True

To summarize these differences:

•         tz_localize(zone) on naive data assigns that zone in place (offsets stay with the wall time). It does not change the hour.

•         tz_convert(zone) on an already-aware timestamp changes the displayed zone and wall-clock value while preserving the underlying instant.

•         tz_localize(None) makes an aware index naive by dropping the zone (keeps wall times).

•         tz_convert(None) converts aware to UTC then drops tz (shifts wall times).

For example:

ts = pd.Timestamp("2020-11-01T01:30:00-08:00")  # fixed-offset time, 09:30 UTC
ts.tz_convert("UTC")  # 2020-11-01 09:30:00+00:00, same instant displayed in UTC
ts.tz_convert("UTC").tz_localize(None)  # 2020-11-01 09:30:00, now naive

In contrast:

ts_naive = pd.Timestamp("2020-11-01 01:30:00")  # naive wall-clock label
ts_naive.tz_localize("America/Los_Angeles")  # raises unless ambiguity evidence is supplied

If we instead call pd.to_datetime("2020-11-01 01:30:00", utc=True), pandas assumes the naive string is 01:30 UTC. In Los Angeles, that instant is 18:30 PDT on October 31. That is an assumption, not an inference from the source, so we do not apply it to C or D. In this audit, the parsing paths are explicit.

•         Offset-aware strings with utc=True (safe, as they include offset).

•         Known-zone naive strings with tz_localize(zone) and handle ambiguous/nonexistent explicitly.

•         Strings with “Z” using utc=True or direct parse to UTC.

•         If any parsing fails (ValueError) or yields NaT, we keep track of it in a “cohort” rather than drop it.

Make mixed-offset and parsing failures visible

The proposed pinned lab should test mixed-offset and malformed inputs rather than relying on undocumented defaults. For example, this case contains two aware strings with different source offsets.

pd.to_datetime(["2020-11-01T01:30:00-07:00", "2020-11-01T02:30:00-08:00"], utc=True)

With utc=True, pandas normalizes both aware inputs into one UTC DatetimeIndex while preserving their distinct instants. A separate test must cover mixed naive and aware strings, because their handling can depend on the input shape and runtime version. The pipeline should classify each input cohort deliberately and record any error or coerced result.

The pandas.to_datetime documentation states that errors='raise' surfaces invalid input while errors='coerce' returns NaT for unparsable entries. The audit tests both paths so that failures are visible rather than silently filtered later.

pd.to_datetime(["2020/07/31", "not_a_date"], errors='raise')   # raises ValueError
pd.to_datetime(["2020/07/31", "not_a_date"], errors='coerce')  # yields [2020-07-31, NaT]

The pipeline captures parsing errors and NaT results in the ledger. A coerced NaT or other missing value must not disappear from a later groupby; it remains an unresolved event with its event ID and amount.

Resolve repeated times only with sufficient evidence

When we encounter ambiguous repeated times, such as event C at 1:30 on November 1, we must decide whether the source evidence identifies one occurrence. The DatetimeIndex.tz_localize reference provides several mechanisms.

•         ambiguous='raise' is the default and surfaces an ambiguous local time as an error.

•         ambiguous='infer' uses the ordering of adjacent values to infer occurrences when the sequence supplies enough information. It is not a general source-of-truth mechanism.

•         ambiguous='NaT' replaces ambiguous entries with NaT.

•         Or one can explicitly supply a boolean array (True for DST, False for non-DST).

We treat these as algorithms, not oracles. For our audit:

•         Event A and B: We have explicit offsets, so there is no ambiguity in our pipeline. We accept them using those offsets.

•         Event C: No offset or occurrence record is available. Both fold=0 and fold=1 remain possible under Python's zoneinfo model. We do not apply ambiguous='infer' automatically or select an arbitrary Boolean. C is quarantined with reason code AMBIGUOUS_REPEATED_HOUR until a source owner supplies evidence.

A decision table for repeated hour might look like:

In practice, C ends up in the quarantined ledger. We assign it to a data steward or source-owner to investigate. Perhaps the source has a log of DST transition. Until then, we do not include C in final aggregates. This way, we never drop counts unnoticed. We simply “park” C with a status like disposition="Hold" and reason="Ambiguous local time".

Handle skipped times without inventing an event instant

For nonexistent local times such as event D at 2020-03-08 02:30 in Los Angeles, the DatetimeIndex.tz_localize reference provides several mechanical handling options.

•         nonexistent='raise' (default) will error.

•         nonexistent='shift_forward' moves the value to the first valid local time, which is 03:00 for this fixture.

•        nonexistent='shift_backward' moves the value to the final representable instant before the gap.

•         nonexistent='NaT' replaces it with missing.

These are mechanical transformations, not new source evidence. Unless the source contract explicitly authorizes a correction policy, shifting can distort the event's meaning. For D, shift_forward would produce 03:00 and could conflate the row with an event actually recorded at that valid time. Shifting backward would be another unsupported guess. The documented nonexistent-time policies therefore remain disabled for this source, and D stays quarantined with its original raw value.

A summary of nonexistent-time handling policies:

•         nonexistent='raise' (default): fails fast, row goes to error/hold.

•         nonexistent='shift_forward': moves to the first valid time (if pre-authorized by policy). Can cause event to migrate to the next date/day.

•         nonexistent='shift_backward': moves just before the gap. Rarely used unless specified.

•         nonexistent='NaT': marks it missing, which we prefer for audit.

Because our goal is evidence-led rather than “just get something out the door,” we do not invent D’s instant. We keep the raw “02:30” and note the failure. If a later business rule says “on that source, treat spring-forward times as the following hour,” we could apply it as a correction with explicit annotation. For now, D stays in the Hold bin.

Separate canonical instants from reporting buckets

Once rows are accepted, the pipeline stores their canonical instants as timezone-aware UTC timestamps. It derives report dates only after converting those instants to the approved reporting zone. Event E is 2020-11-01 06:30:00+00:00 in UTC and 2020-10-31 23:30:00-07:00 in America/Los_Angeles, so its report_date is 2020-10-31 rather than 2020-11-01.

We explicitly define the reporting zone (here “America/Los_Angeles”) and compute:

utc_inst = pd.Timestamp("2020-11-01T06:30:00Z")  # event E instant in UTC
report_zone = "America/Los_Angeles"
local_inst = utc_inst.tz_convert(report_zone)
report_date = local_inst.date()
# report_date is 2020-10-31

This preserves the approved local-day boundary. Calling date() on the UTC value first would place E on November 1, which violates the reporting contract. The release manifest therefore records that report dates are derived after conversion to America/Los_Angeles.

Derive the local reporting date from the aware instant

Consider event F at 2020-11-01T08:30Z. Its UTC instant is 2020-11-01 08:30 UTC. Converting to LA:

instant_F = pd.Timestamp("2020-11-01T08:30:00Z")       # 2020-11-01 08:30 UTC
local_F = instant_F.tz_convert("America/Los_Angeles")  # 2020-11-01 01:30 PDT
report_date_F = local_F.date()                            # 2020-11-01

The 08:30Z instant remains on November 1 in Los Angeles, while E at 06:30Z belongs to October 31. The DatetimeIndex.tz_convert operation changes the displayed zone without changing the instant. The manifest records America/Los_Angeles as the reporting zone and states that local dates are derived only from aware instants.

Never use repeated wall time as an event identity

Two events can share a local timestamp, and separate event IDs can even share one canonical instant. A and B share the 01:30 wall-clock label but have different instants; A and F share the 08:30 UTC instant but remain distinct source events. Deduplicating by raw timestamp, instant, or report date alone could remove legitimate rows. Any duplicate rule must use the approved event-identity key, beginning with event_id.

This also means that an event before and after DST fall-back (with the same wall time) may end up with different instants but share a label. They must remain separate rows. Any deduplication logic must be event-ID aware. For example:

df = pd.DataFrame({
    "event_id": ["A","B"],
    "local_time": ["2020-11-01 01:30","2020-11-01 01:30"]
})
# do NOT do: df.drop_duplicates(subset=["local_time"])

Even after parsing, we might have two rows in the final DataFrame with the same wall time string but different offsets. That’s okay: they contributed separately to the sum.

Reconcile rows and amounts across every disposition

For this run, row disposition is either Accepted or Quarantined. Release state is tracked separately as Held, Published, or Restated. Each event must appear exactly once in the row-disposition ledger, and counts and integer amounts must reconcile.

This ledger is kept in our logs. In addition, we break down accepted events by each grouping to double-check:

•         Accepted by UTC date: 2020-11-01 contains A, B, E, and F; count 4; amount 4,500.

•         Local date 2020-10-31: event E; count 1; amount 900.

•         Local date 2020-11-01: events A, B, and F; count 3; amount 3,600.

The accepted subtotals reconcile: 900 + 3,600 = 4,500. The quarantined 3,200 units remain visible and unpublished. The full input total of 7,700 units is preserved; the pipeline has split rows by disposition without losing or inventing amounts.

Each quarantined row (C and D) is logged with a reason and owner field in the database. For example:

Owner fields are assigned according to organizational responsibility. The release documentation proves that input events equal accepted events plus quarantined events, by event ID, count, and amount. Any report must start from this reconciled state.

Compare the legacy and candidate pipelines by event ID

Before finalizing, we do an event-level diff: what would a legacy pipeline have done versus our candidate logic? For illustration, imagine a naïve pipeline that simply did pd.to_datetime with utc=True on all rows and then grouped by date. We contrast that with our results:

In this hypothetical comparison, the legacy pipeline assumes that naive C and D are UTC. It therefore places C on October 31 in Los Angeles and D on March 7, even though neither interpretation is supported by the source. The legacy pipeline still sums to 7,700 units, but the balanced total hides unsupported event instants and bucket assignments. The candidate pipeline accepts 4,500 units and holds 3,200 units with explicit reasons. Every event-level difference and every affected bucket must be explained before release.

Only after every changed or missing row is explained should the team consider performance work or engine changes. The article on engine comparisons after correctness is established provides complementary context; this audit remains about time meaning and reconciliation, not speed.

Protect time meaning across storage and reload

We also audit the selected storage and reload path. A CSV round trip can lose type metadata or require explicit parsing, while database and columnar formats have their own schemas and adapters. The test must target the actual format and configuration rather than assume universal behavior. These controls belong within the broader analytics workflow, but time meaning still needs a dedicated round-trip assertion.

•         Time zones are preserved if needed (or at least recovered).

•         The numeric precision (ns vs ms) is unchanged.

•         No default parser setting causes a reinterpretation.

The version manifest records the exact Python and pandas runtime versions used by the executable lab. Python's zoneinfo documentation explains that time-zone data can come from the operating system or the tzdata package. Record the actual source and version or system database identifier so that another environment can reproduce the same historical rules.

For example, if we export the DataFrame to Parquet, we then reload it in a fresh process:

# Example round-trip test (conceptual):
df.to_parquet("events.parquet")
df2 = pd.read_parquet("events.parquet")
assert df2["instant"].dt.tz == df["instant"].dt.tz  # zone preserved

If the zone was stripped, we should catch it (and have a rule to re-apply it). Similarly, if writing to CSV:

df.to_csv("events.csv", date_format="iso")
df3 = pd.read_csv("events.csv", parse_dates=["instant"])

We confirm that df3["instant"][0] matches the original instant. Any mismatch must be flagged. For instance, if a DB column stored a datetime without timezone, our pipeline should convert it back explicitly or fail. We never silently assume “UTC timezone” if it isn’t stored.

If a storage choice inherently loses time-zone info, our manifest notes “the output will be naive UTC; the reporting zone will be re-applied on read.” We err on the side of failure (loud error) if the saved timestamp isn’t what we expect. That way a broken reload requires explicit fix, not hidden data loss.

Monitor quarantine and own reporting corrections

Quarantined rows need named owners and review deadlines. Event C may go to the source-data owner for an offset or occurrence record. Event D may go to the system owner to confirm whether another timestamp, correction log, or source policy identifies a valid instant. We maintain separate release outputs.

•         Published output: only accepted rows, aggregated, ready for business reporting.

•         Held output: the quarantined rows with reasons, not yet included in any report.

We include the reporting time-zone (America/Los_Angeles) in every output’s metadata so downstream users know how dates were computed. Each time we publish or hold, we log a “Quarantine Dashboard” entry with the reason and person who acknowledged it. Any later correction (say the team decides C was a fold=1) would create a new row disposition (“Corrected”) and trigger a restatement run, not a silent overwrite.

The handoff is key: the analytics owner should not just fix C quietly in the data set; they must update the ledger. For example, if event C is later confirmed as 1:30 AM PST (fold=1), we would move it from status="Hold" to status="Restated", record its new UTC instant (09:30Z) and the new report date, and rerun the aggregates. The original published report would remain unchanged until a formal restatement is approved. This disciplined approach prevents surprise changes in totals.

This handoff separates data-side analysis from reporting approval. The discussion of reporting ownership and analytical interpretation provides complementary context; the ledger itself names who owns the contested source evidence and who authorizes a report correction.

Recover a bad time transformation reproducibly

If an audit uncovers a bad transformation after publication, rerun the pipeline from preserved raw data and the prior manifest. Suppose a source owner later authorizes nonexistent='shift_forward' for D. The previously published output is not treated as correct merely because it was published; the recovery follows controlled steps.

1.      Reload the original raw input (ensuring raw_timestamp and source_zone are intact).

2.      Apply the old transformation logic to ensure we get the same published values (verifying reproducibility).

3.      Update the code to the new rule (nonexistent='shift_forward') for event D.

4.      Re-run only the affected subset (C and D) or the whole flow, capturing new instants.

5.      Compute the affected buckets and ledgers. Under pandas shift_forward semantics, D moves to 03:00 on March 8 in the local reporting zone, and its 700 units move from quarantine into the accepted total.

We then produce a restatement ledger: listing original vs new instants/dates for changed events, and delta in totals. This document goes to the reporting owner for sign-off. For example:

The raw data and prior transformation manifest remain the evidence base. Publication history is not proof that the old interpretation was correct. Replayability is possible because the pipeline did not overwrite the source timestamp or silently discard unresolved rows.

Build analytics foundations for temporal-data reviews

Temporal-data reviews depend on foundations in Python, R, SQL, data exploration, analysis, visualization, and disciplined project work. The Refonte Learning Data Analytics Program page lists a three-month format at 12-14 hours per week and names Python, R, SQL, Tableau, Excel, data exploration, analysis, visualization, and practical industry projects. The checked page does not establish a dedicated pandas DST or timestamp-provenance module; missing source evidence still requires operational ownership.

•         Data exploration: Plot event counts over time to spot anomalies (e.g. a missing hour).

•         Validation checks: Use descriptive stats and manual edge-case tests (as we’ve done) to verify every scenario.

•         Code tests: Write unit tests for ambiguous/nonexistent cases.

•         Documentation: Record time-zone and DST assumptions as part of pipeline docs.

These foundations support the workflow, but no training program can supply a missing source offset or occurrence record. The acceptance policy, quarantine process, and reporting decision must remain explicit and reviewable.

Publish only with an explained time and total ledger

Before we release the final report, we bundle everything into a release manifest: the row-disposition ledger, accepted-instant evidence, reporting zone contract, and version manifest. This includes:

•         The reconciliation table of accepted vs quarantined events (with reasons) shown above.

•         The list of accepted events with their UTC instants and derived local report dates.

•         The exact pandas and Python runtime versions plus the time-zone database source and version used by the lab.

•         Any unresolved holds (C, D) and next steps.

We explicitly avoid stating “we fixed it” without justification. The published report will carry a statement like: “Totals include events processed up to Nov 1, 2020 in UTC and then bucketed by America/Los_Angeles date, as detailed in the ledger. Two events were held for review.” A naive pipeline that simply “parses everything” would not have this ledger of decisions. In contrast, our approach means the system knows which rows were ambiguous and which were not, even if the final published numbers are only for accepted rows.

In conclusion, parsing is not the end goal: accountable time meaning is. By preserving raw evidence, quarantining problematic rows, and balancing the ledgers, the pipeline keeps every row and total accounted for. A pipeline that parses every value is not automatically a pipeline that knows when every event happened.