Timezones and Day Boundaries: Whose Midnight Defines the Day?

A user in Tokyo opens your app at 9pm and sees 4,000 steps. They have been out all day; they know that number is wrong. Nothing in your sync layer is broken — every sample arrived, nothing was duplicated, the raw table holds exactly what the watch recorded. The bug is that your daily rollup buckets on the UTC instant, so "today" for that user runs from 09:00 Tokyo time to 09:00 the next morning, and nine hours of their day are filed under yesterday. Timescale's own documentation states the mechanism plainly: a continuous aggregate over timestamp with time zone aligns to the UTC time zone. Every non-UTC user is wrong, every day, in a way that no amount of testing in your own timezone will ever surface.

The design decision that prevents this, and the class of bugs around it, is a storage decision made at ingest: keep the instant, keep the UTC offset in effect at that instant, and write the civil local date as a real column. Then decide, deliberately and in writing, whose midnight defines the day. This page is about both halves. If your symptom is instead that data arrives hours after the workout, that is a different problem — see wearable data arriving late.

Three columns, not one

Everyone starts by storing the instant in UTC, because that is the advice, and the advice is right as far as it goes. It goes about halfway.

The middle row is why "just store local time" is the worst of the three options, not a lazy-but-workable one. On the autumn transition local 01:30 occurs twice, an hour apart in reality. A sleep session or a workout logged in that window has two valid instants and you have thrown away the discriminator. It is unrecoverable — no later query, no user prompt, no clever heuristic gets it back.

The IETF standardised a format for exactly the fourth row. RFC 9557 (the sedate working group's extended date-time format) exists, in its own words, because applications "handle each such instant in time with an associated time zone name, in order to take into account events such as daylight saving time transitions". Its example, 1996-12-19T16:39:57-08:00[America/Los_Angeles], "represents the exact same instant in time as the previous example but additionally specifies the human time zone associated with it". The draft is also explicit about why the name carries information the offset does not: "the additional information conveyed by using that time zone name is to change with any rule changes as recorded in the IANA time zone database." The offset tells you what happened. The zone ID tells you what will happen if the rules move.

Two rules that follow directly, both from the tz database maintainers themselves. First, never store zone abbreviations: "these abbreviations are ambiguous in practice: e.g., CST means one thing in China and something else in North America, and IST can refer to time in India, Ireland or Israel." Second, treat tzdata as a dependency with a release cadence. The maintainers design IDs to "be robust in the presence of political changes", which is a polite way of saying the rules change, and a stale copy of the database silently changes your historical arithmetic.

The schema

Concretely, per sample:

CREATE TABLE sample (
  user_id        uuid             NOT NULL,
  provider       text             NOT NULL,
  external_id    text             NOT NULL,
  metric         text             NOT NULL,

  -- when it happened, absolutely
  start_instant  timestamptz      NOT NULL,
  end_instant    timestamptz      NOT NULL,

  -- what the user's clock said, reconstructably
  start_offset_s integer,          -- seconds east of UTC; NULL = provider gave none
  zone_id        text,             -- IANA name; NULL if unknown
  zone_source    smallint NOT NULL, -- provider / device-reported / inferred / defaulted

  -- what the product means by "the day this belongs to"
  local_date     date             NOT NULL,
  day_rule       smallint         NOT NULL,

  value          double precision NOT NULL,
  recording_method smallint       NOT NULL
);

Four things in there are load-bearing and are the ones teams leave out.

start_offset_s is seconds, not hours. Not every zone is a whole number of hours off UTC, and an integer hours column silently corrupts the users who live in the ones that are not.

zone_source records how confident you are. As the platform section below shows, a meaningful share of your offsets are guesses made by somebody else's default-value logic. Storing the offset without storing whether it was observed or inferred means you cannot later tell a user's genuine travel history apart from your own back-filling.

local_date is written at ingest, from (instant, offset), and never derived on read. Converting on read means every daily query needs the user's full zone history in scope and cannot use a plain index; it also means the answer to "what were their steps on the 14th" changes depending on where the user's phone is when they ask. Denormalising one column buys you a daily rollup that is indexable, DST-proof and travel-proof.

day_rule versions the policy that produced local_date. You will change that policy at least once. Knowing which rows were computed under the old one is the difference between a recompute and an archaeology project.

The rollup on top is keyed on the civil date and is recomputed, never incremented:

CREATE TABLE daily_rollup (
  user_id     uuid    NOT NULL,
  metric      text    NOT NULL,
  local_date  date    NOT NULL,
  source      text    NOT NULL,
  value       double precision NOT NULL,
  day_rule    smallint NOT NULL,
  computed_at timestamptz NOT NULL,
  PRIMARY KEY (user_id, metric, local_date, source)
);

The design of the storage layer underneath this — partitioning, rollup refresh, the dirty-day queue — is time-series storage for health data. The point here is only that the rollup key is a date, not a time range.

What DST actually does to your product

Take America/New_York and the two 2026 transitions.

A step goal is stated as a per-day quantity but achieved at a roughly per-hour rate. So twice a year every user in a DST-observing zone gets a day that is about four percent easier or harder than every other day. That is fine — it is a fact about the calendar, not a bug — as long as your code agrees with the calendar. Three places it usually does not:

Streaks. Written the obvious way, a streak evaluator asks whether the gap since the last qualifying day is under some number of seconds. On the 25-hour autumn day, two genuinely consecutive days can be more than 24 hours apart and the streak breaks. Write streaks as successor arithmetic over local_date — 2026-11-01 is followed by 2026-11-02, full stop — never as a duration comparison. A streak is a property of the calendar. A rest interval is a property of the clock. Compute durations from instants, compute day membership from civil dates, and never let one leak into the other.

Sleep windows. "From local 23:00 to local 07:00" is nine real hours on the autumn night and seven on the spring one. Subtract civil times and you report a wrong duration twice a year for every user. Subtract instants and you are right — and the user's chart will legitimately show a night an hour longer than the wall clock suggests, which is correct and worth a tooltip.

Per-hour rates. Any "average steps per hour", "time in zone as a fraction of the day", or intraday normalisation that divides by 86,400 is wrong on those two days. Divide by the actual instant-difference between the day's boundaries.

Travel: partial days and doubled days

Now the case that produces support tickets rather than rounding errors. A user flies LAX to LHR: departs 2026-06-10 at 20:00 PDT (2026-06-11T03:00Z) and lands 2026-06-11 at 14:00 BST (2026-06-11T13:00Z). Their phone flips from -07:00 to +01:00 on landing.

Under the rule "the day is defined by the offset in effect at each sample's own timestamp":

• Local date 2026-06-10 runs 2026-06-10T07:00Z to 2026-06-11T07:00Z. Normal, 24 hours.
• Local date 2026-06-11 starts at 2026-06-11T07:00Z and ends at local midnight BST, 2026-06-11T23:00Z. Sixteen hours of real time, with an eight-hour hole in the middle where their clock jumped from 06:00 straight to 14:00.

Two consequences you have to handle explicitly. The user's goal for June 11 has to be hit in sixteen hours, and if your goal logic has any notion of pace-versus-remaining-time it will be badly wrong. And those eight missing hours of local time are a gap that is not a data-quality problem — your gap detector will flag it as one, and only the offset column lets you tell the two apart.

Westbound is the mirror and is worse for one metric in particular: a 32-hour local day, in which the user can plausibly log two main sleep sessions on the same civil date. A sleep pipeline that assumes at most one primary sleep per local_date — and most of them do, usually implicitly, via an upsert — will silently discard one of them.

What the platforms give you

Five things are worth knowing in detail, because each one produces a different wrong column.

Health Connect records carry an offset, and some of those offsets are guesses

Instantaneous records carry zoneOffset; interval records carry startZoneOffset and endZoneOffset. Google documents that "All data written to Health Connect must specify the zone offset information", so that reading apps can "represent it in civil time" — civil time being, in Google's definition, "the time that is local and relevant to the user, but not necessarily in Coordinated Universal Time (UTC)".

Treat the value as a hint rather than as provenance. Google documents that on Android 14 and higher, if the offset is not available, Health Connect sets it from the device's system default time zone — which is where the phone is, not necessarily where the activity happened. On Android 13 and lower, writing without an offset is possible, and Google's guidance is that your app "needs to be prepared to deal with both kinds of data".

What you must do: persist the offset on every record, and write into zone_source whether it came from the writer or from the platform's own default.

Duration and Period are not the same slice

aggregateGroupByDuration slices by a Duration — fixed elapsed time. aggregateGroupByPeriod slices by a Period — a calendar unit. Only the second one means "a day".

Google documents that period bucketing requires a LocalDateTime-based TimeRangeFilter.between(...), and that passing Instant values instead returns an IllegalStateException. Google does not spell out how a LocalDateTime filter resolves against stored records.

What you must do: use the Period variant for anything the user sees as a day, and do not build a rollup whose correctness depends on a particular resolution mechanism there.

Writing without an offset rewrites the user's travel history

The AndroidX source comment on IntervalRecord says the offset is the "User experienced zone offset at startTime, or null if unknown", and that "Providing these will help history aggregations results stay consistent should user travel. Queries with user experienced time filters will assume system current zone offset if the information is absent."

Read that last sentence twice. Write without an offset and the platform back-fills the user's current offset, which rewrites the history of every trip they ever took.

What you must do: always write an offset, and never default it to ZoneOffset.UTC — Google's instruction is to "calculate the offset based on the device's actual location".

HealthKit samples carry no calendar at all

HKSample carries startDate and endDate as Date values: absolute instants, no calendar and no zone attached. HKMetadataKeyTimeZone exists and takes a name string compatible with NSTimeZone, but Apple documents it as writer-supplied and only recommends it, specifically for sleep — "For best results when analyzing sleep samples, it's recommended that you store time zone metadata with your sleep sample data." Apple does not document whether first-party iPhone and Watch step samples populate it, so assume they may not.

What you must do: capture the device's current zone ID on the client at read time, ship it up alongside the batch, and record it in zone_source as device-reported rather than observed.

Apple resolves the day boundary at query time, on the device

HKStatisticsCollectionQuery builds intervals from an anchorDate plus intervalComponents. Apple documents the anchor as an arbitrary absolute instant, with equal-length intervals tiling outward from it in both directions and no gaps, and Apple's own sample code derives the anchor from Calendar.current and its timeZone.

So the boundary is resolved against the device's zone at query time, not against the zone the activity happened in. Two devices in two zones will partition the same samples differently, and a previously reported daily total can change with no data change at all. Apple documents fixed-length intervals rather than calendar-following ones, so do not assume buckets stay pinned to local midnight through a DST transition.

What you must do: reconcile server-side against your stored local_date rather than trusting a daily figure the client computed for you.

Both platform guides — Health Connect and HealthKit — cover the read mechanics. The pattern worth extracting is that Android hands you a possibly-guessed offset and Apple hands you nothing, and your ingest adapter has to normalise those two into the same three columns with an honest confidence flag.

When providers disagree about what a timestamp is

This is the one that survives code review. Some provider endpoints return full instants. Others return a bare date string — "2026-06-11" — for a daily summary, computed by the provider in whatever timezone it holds in the user's profile setting, which you cannot see and the user probably set once in 2021.

The naive join is daily_summary.date = date(sample.timestamp_utc). It runs, it returns rows, every row looks plausible, and it is wrong for exactly the users whose actual zone differs from the profile setting the provider assumed — which is a small, invisible, growing fraction of your users, weighted towards the ones who travel. Nothing errors. The numbers just quietly stop agreeing between two screens.

The rule we would enforce at the adapter boundary: a provider's bare date field is a label, not a timestamp. Store it as provider_local_date if you need it for reconciliation, and never derive an instant from it, because the instant is not recoverable from a date string with no offset. Any join between a date-keyed feed and an instant-keyed feed has to go through your own local_date, computed by your own rule, from data that carries an offset. The adapter's job is not to pass through the provider's shape; it is to produce your three columns or admit that it cannot.

Whose midnight? Pick one and write it down

There is no correct answer here. There is only a consistent one, and the failure mode is not picking.

What we would actually build

Our default is the first: the day the user experienced is the day they get. Two obligations come with picking it. Write it into your API docs, because it is externally visible behaviour, not an implementation detail. And make every endpoint agree — the daily-series endpoint, the weekly-total endpoint, the streak evaluator and the export must all resolve "day" through the same local_date. The bug users actually report is not that a number is wrong; it is that two screens in your app disagree, which reads as the app being broken rather than the calendar being awkward.

Never adopt the second row as an accident. It is what you get by default if you skip the offset column and let the platform or your query layer fill in the current zone, and it is the single most destructive option because it is unrecoverable: once you have re-bucketed a year of history against a zone the user was not in, the original assignment is gone.

Buy or build

Most of the work above is boring and identical for every provider: read the timestamp, find the offset if there is one, decide what to do when there is not, write three columns. A health-data aggregator does that across every provider it supports, and for most teams that is straightforwardly the right trade — you are not going to out-engineer a vendor whose entire product is provider adapters, and the work does not differentiate you. See health data aggregator APIs and what a health data aggregator is.

What buying does not remove is the previous section. An aggregator can hand you a normalised instant and, where the provider supplies one, an offset. It cannot decide whose midnight your streak uses, because that is a statement about your product. We would build the rollup layer in-house when the day definition is itself a feature — a shift-worker wellness product where the "day" is a shift, a fasting app where the boundary is the eating window, a coaching product that anchors weeks to a training block. In those cases the aggregator is still a fine source; it is the civil-date layer that has to be yours.

Recompute is cheap only if you designed for it

Everything above will occasionally be wrong, and the question that matters is what the fix costs. Three things retroactively change a local_date you already wrote: you learn the offset you assumed was wrong, you change the day rule, or tzdata ships a release that changes the offset in effect for some instant. That last one is not hypothetical — the tz maintainers design the database to be "robust in the presence of political changes" precisely because governments keep making them.

If your daily rollup is a pure function of (user_id, metric, local_date, source) over immutable raw samples, all three fixes are the same operation: re-derive local_date for the affected samples, mark those days dirty, recompute those cells. Bounded, resumable, auditable, and answerable to "is this number right?" by recomputing one cell. If your daily total is instead an incremented counter, there is no fix — you cannot prove a counter is correct and you cannot repair one without recomputing anyway, so you carry all of the cost and none of the benefit. That is the same argument as metric versioning and recompute, and it is why day_rule is a column rather than a constant in your code.

The honest limits

This design does not make the offset true. It makes it recorded, which is a smaller claim than it sounds.

A user who never changes their phone's timezone setting while travelling hands you a confidently wrong offset, and nothing in the data reveals it. Health Connect's back-fill from the device default zone and HealthKit's optional, sleep-oriented metadata key both mean a real share of your history has an offset that somebody's default-value logic invented. zone_source lets you distinguish those rows later; it does not make them correct.

Two things are genuinely undocumented and we will not pretend otherwise. Apple does not document whether first-party step samples carry HKMetadataKeyTimeZone, so do not build a plan that depends on the answer. And Google does not spell out how a LocalDateTime-based TimeRangeFilter resolves against stored records — whether against the record's own offset, the device's current zone, or something else — so do not encode an assumption about that mechanism into a rollup.

Finally, the offset columns do nothing about a related-but-separate class of problem: samples that arrive days late, get retro-edited by the user, or get rewritten by the platform months later. Those change which days are dirty, not how a day is defined, and they belong to the incremental-sync layer. The three columns are what make the repair cheap when they happen.

Pick the definition. Write it in the docs. Store the offset even when you think you will never need it, because the one column you cannot add later is the one that records where the user was.