Magnadata All articles
Data Governance

The 01:30 That Happens Twice: A Practical Review of Time Handling in UK Data Estates

Magnadata
The 01:30 That Happens Twice: A Practical Review of Time Handling in UK Data Estates

Photo by Photo by Mark Higham @theartshot360 on Unsplash on Unsplash

Most UK data teams do not set out to mishandle time. They inherit it. A source system records a local timestamp, a warehouse stores it without an offset, a dashboard groups it by day, and a finance extract applies a fiscal calendar. Each step seems reasonable in isolation. Together, they can produce a number that is wrong by an hour, a day, or a duplicate — and no error message appears.

The autumn transition on 25 October 2026 has just passed. That makes the coming weeks a sensible moment to review how time is represented across the estate, before the next reporting cycle and the next set of year-end extracts. The issue is not only the hour that disappears in March or repeats in October. It is the governance question beneath it: does the organisation know, field by field, what its timestamps mean?

Why the clock change is a data test, not a calendar curiosity

In the UK, clocks go forward on the last Sunday in March and back on the last Sunday in October. In 2026 those dates were 29 March and 25 October. At 01:00 GMT on 29 March, local time jumps to 02:00 BST; 01:30 never occurs. At 02:00 BST on 25 October, local time falls back to 01:00 GMT; 01:30 occurs twice.

A naive local timestamp of 2026-10-25 01:30:00 is therefore ambiguous. If the warehouse stores UTC, the two events remain distinct: 00:30 UTC and 01:30 UTC. If an operational system stores local time without an offset, it cannot tell them apart. If a dashboard deduplicates on the local timestamp, it may drop one event. If a scheduled job is set for 01:30 local time on 29 March, it may not run at all, or it may run twice depending on the scheduler’s behaviour.

These are not exotic failures. They appear in payment logs, claims processing, website event streams, logistics scans, contact-centre records and batch jobs. They tend to surface as small reconciliation differences that are explained away rather than fixed.

A half-day audit: four moves

The following review can be completed in roughly half a day by a small group: a data engineer, an analytics lead and a representative from the business area that owns the report. The aim is not to fix everything. It is to produce evidence and a short list of explicit decisions.

1. Inventory the clocks (about 40 minutes)

List every field that carries a time or date: created_at, event_time, transaction_date, submitted_on, period_end, and so on. For each, record the system of origin, the data type, and one of four semantics:

Most estates will contain all four. The point is to make the mixture visible. A field named timestamp is not a specification.

2. Trace one transaction across a transition (about 60 minutes)

Choose a real transaction that crossed the 25 October 2026 boundary — a card payment, an order, a claim or a case note. Retrieve its record from the source system, the warehouse, the dashboard and any finance or regulatory extract. Compare the times.

Ask three questions. Does the source record contain an offset? Does the warehouse preserve enough information to distinguish the first 01:30 from the second? Does the dashboard state which time zone it displays? If the answer to any of these is no, the estate has a time-semantics defect. It may not have caused a visible error yet, but it is a latent one.

3. Test the business calendars (about 35 minutes)

Reporting calendars rarely align with daylight saving. The UK tax year runs from 6 April to 5 April. Retail and supply-chain teams may use 4-4-5 calendars. Weekly reporting may start on a Sunday or a Monday. Month-end extracts may be scheduled in local time.

Pick the reports that matter most and check what happens on 29 March and 25 October. Does a daily total include 23 or 25 hours? Does a weekly total cross the transition cleanly? Does a scheduled extract run before or after the clocks change? The answers should be documented, not discovered during an audit.

4. Read the contracts and controls (about 45 minutes)

Time semantics are also a procurement issue. Data-sharing agreements, vendor SLAs and platform contracts often specify delivery times in local time without defining which local time. A supplier that promises a file by 07:00 may mean 07:00 GMT in winter and 07:00 BST in summer. That is a one-hour difference twice a year, and it matters for regulatory reporting and operational cut-offs.

Add a short clause to the review: every time-related commitment should state the time zone, the offset convention and the behaviour at transitions. Internally, assign an owner for time semantics. It is a governance artefact, not a setting buried in a BI tool.

A short checklist for the next reporting cycle

The hour that vanishes is a useful test case because it is predictable. It arrives twice a year, on a published schedule, and it exposes the places where time is treated as an incidental column rather than a governed dimension. A half-day review will not resolve every ambiguity. It will, however, show where the ambiguities are — and that is the precondition for making them explicit.

All Articles

Related Articles

The Prompt Ledger: A Half-Day Review of AI Chat Histories in UK Data Estates

The Prompt Ledger: A Half-Day Review of AI Chat Histories in UK Data Estates

Where the Opt-Out Stops: A Half-Day Suppression Test for UK Marketing Data

The Answer Key Is the Contract: Grading Natural-Language SQL Before It Touches a UK Production Warehouse

The Answer Key Is the Contract: Grading Natural-Language SQL Before It Touches a UK Production Warehouse