Storing UTC vs Local Time

Concepts covered: Timezones, Timestamp Types, Date vs Timestamp

This question often arrives as a design prompt with no time zones in it: "design the bookings table," while the interviewer watches what type you give created_at. Or it arrives as a symptom: "a booking made at 11:30pm in Honolulu shows up on tomorrow's report," or "daily numbers shifted when someone changed the warehouse session zone." The storage choice is made once, and every reader of the table inherits it.

What each choice commits readers to

Store the UTC instant

• Ordering and durations across regions are exact
• Every local-day question needs a conversion
• Every reader must know which zone defines the day
• Daylight saving is handled by the conversion, never by the data

Store local wall-clock time

• A local day is a plain DATE cast for a single market
• Events in different zones cannot be ordered against each other
• 01:30 happens twice on the night clocks fall back
• Converting to any other zone needs a zone nobody stored

Put it the way the interviewer wants to hear it: UTC storage moves work onto the reader, and local storage makes some questions impossible to answer at all. Work can be paid for; lost information can't be recovered, which is why the UTC instant is the default.

Instant, zone and local date

The complete answer stores the instant, in a type that means an absolute point in time, and the IANA zone of whatever defines the business day, here the property's America/Los_Angeles, since the guest's zone and headquarters' zone answer different questions. Where readers filter by local day constantly, it also stores a local_date column written at load time, so nobody repeats the conversion and the partition key can match it. The type names differ by engine, and naming the right one is part of the answer:

A local day expressed in UTC

Reading a local day out of UTC storage converts the day's boundaries, never each row. The Los Angeles day of 2024-03-14 runs from 07:00 UTC that morning to 07:00 UTC the next, because clocks sprang forward on March 10 and the offset is now 7 hours. Bounds on the bare timestamp keep the filter in the form the planner can prune on.

SELECT COUNT(*) AS bookings,
  SUM(amount) AS revenue
FROM bookings
WHERE created_at_utc >= '2024-03-14 07:00:00'
  AND created_at_utc < '2024-03-15 07:00:00'

Grouped by UTC date, March 14 would hold bookings 1 through 3 for $70. The Los Angeles day holds bookings 2 through 4 for $90: the same count, different bookings, $20 apart. Those bounds also move twice a year, from 08:00 UTC before March 10 to 07:00 after, so they belong in the calendar table as day_start_utc and day_end_utc per zone and date, computed once, rather than as an offset hardcoded in each query.

Storage zone and partition date

The zone choice decides which partitions a local query touches. Partition by UTC date and a single Los Angeles day spans 2 partitions, so the query needs the timestamp bounds plus both partition dates when the partition column is separate from the timestamp. Partition by local date and a per-market day prunes to 1, but a global "last hour" query has to read every local date that hour touches. Offsets run from UTC-12 to UTC+14, so a single instant can sit on 3 calendar dates at once. Partition on whichever date most readers filter on, and write down which one you picked.

Future times break the rule

The UTC default has a single exception, and naming it is a strong signal. A check-in scheduled for 15:00 local time next summer is a promise about a local clock reading. Convert it to UTC today and the conversion freezes today's daylight-saving rules; if a government changes them before the date, the stored instant points an hour off. Mexico abolished daylight saving time across most of the country in October 2022, so summer 2023 appointments converted under the old rules landed at the wrong hour. Store future events as local wall-clock time plus zone, and convert when the moment arrives.

Do

• Densify at read time with a spine bounded to each entity's active dates
• Fill zero only for days the source confirms it loaded; leave the rest NULL
• Keep each market's business days and holidays in a single owned calendar table
• Convert ranges to half-open once, split by overlap join, and allocate by cumulative rounding
• Store instants in UTC with the zone that defines the day; store future appointments as local time

Avoid

• Wrap the partition column in a function and trust the result rows to reveal the cost
• Group a range by its start month when the question counts nights or days
• Hardcode a UTC offset that daylight saving changes twice a year
• Bound an incremental load with a subquery the planner can't resolve before reading

About this lesson

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.