How Should Databases Handle Dates, Times, and Time Zones?
Learn how to correctly store dates, times, and time zones in a database using UTC and avoid daylight-saving bugs.
Expected Interview Answer
The safest approach is to store every timestamp in UTC using a timezone-aware type, and convert to a user's local time zone only at the presentation layer, so the stored value is always unambiguous regardless of where it is read from.
A plain DATETIME/TIMESTAMP without timezone info records a wall-clock value with no context, so the same stored value can mean different real moments depending on which time zone the reader assumes — a serious bug source across daylight saving transitions and multi-region applications. A "timestamp with time zone" type (or storing UTC explicitly plus a separate zone identifier when local wall-clock time must be preserved) removes that ambiguity: the database always has one canonical instant, and each client converts it for display using its own offset. Applications must also store the IANA zone name (e.g. "America/New_York") rather than a fixed UTC offset when a recurring local time matters, since offsets shift with daylight saving but zone rules do not.
- Eliminates ambiguity about which real-world moment a timestamp represents
- Avoids daylight-saving-transition bugs in scheduling logic
- Makes cross-region data comparable without manual offset math
- Keeps sorting and range queries on timestamps correct globally
AI Mentor Explanation
Announcing a match start time as simply "3 PM" is ambiguous the moment fans in different countries tune in, since 3 PM in London is not 3 PM in Mumbai. Broadcasters instead publish the match start as a single fixed UTC instant, like 14:00 UTC, and each local broadcaster converts it to their own region's clock for the on-screen graphic. A database storing match times in UTC works the same way — one unambiguous instant, converted for display per viewer.
Step-by-Step Explanation
Step 1
Store timestamps in UTC
Use a timezone-aware TIMESTAMPTZ/TIMESTAMP WITH TIME ZONE column so the stored value is one unambiguous instant.
Step 2
Store the IANA zone name when local time matters
For recurring local events (e.g. a 9 AM daily reminder), also persist a zone identifier like "America/New_York", not just a fixed offset.
Step 3
Convert only at the presentation layer
Application code converts the stored UTC instant to each user's local time zone only when rendering, never when storing or comparing.
Step 4
Account for daylight saving in recurring logic
Recompute local offsets from the zone rules for each occurrence, since a fixed offset stored once will become wrong after a DST transition.
What Interviewer Expects
- Recommendation to store timestamps in UTC using a timezone-aware type
- Explanation of why a bare local DATETIME is ambiguous
- Awareness that daylight saving requires storing zone names, not fixed offsets, for recurring events
- Understanding that conversion belongs at the presentation layer, not in storage
Common Mistakes
- Storing local wall-clock time with no time zone information at all
- Storing a fixed UTC offset instead of an IANA zone name for recurring local events
- Performing timezone conversion in the database query instead of consistently at render time
- Assuming all servers and clients share the same time zone by default
Best Answer (HR Friendly)
“I always store timestamps in UTC using a timezone-aware column type, so the database has one unambiguous instant no matter where the data was written from. I only convert to a user's local time when displaying it, and for recurring local events I store the actual time zone name, not just an offset, so daylight saving changes are handled correctly.”
Code Example
CREATE TABLE Events (
event_id BIGINT PRIMARY KEY,
starts_at TIMESTAMPTZ NOT NULL, -- stored as one unambiguous UTC instant
local_zone TEXT NOT NULL -- e.g. 'America/New_York', for recurring local display
);
INSERT INTO Events (event_id, starts_at, local_zone)
VALUES (1, '2026-07-18 14:00:00+00', 'America/New_York');
-- Convert to the event’s own local time only at read/display time:
SELECT starts_at AT TIME ZONE local_zone AS local_display_time
FROM Events
WHERE event_id = 1;Follow-up Questions
- Why is storing a fixed UTC offset different from storing an IANA time zone name?
- How does daylight saving time break naive recurring-event logic?
- What is the difference between TIMESTAMP and TIMESTAMPTZ in PostgreSQL?
- How would you correctly sort events from users in different time zones?
MCQ Practice
1. What is the safest default for storing timestamps in a database?
Storing UTC in a timezone-aware column gives one unambiguous instant regardless of where the data is read.
2. Why should a recurring local event store an IANA zone name instead of a fixed UTC offset?
A fixed offset stored once becomes wrong after a daylight-saving transition, while a zone name lets the correct offset be recomputed for each occurrence.
3. Where should conversion from UTC to a user's local time generally happen?
Storage should remain the unambiguous UTC instant; conversion to a viewer-specific local time belongs at render time.
Flash Cards
What time zone should timestamps be stored in? — UTC, using a timezone-aware column type.
Why is a bare local DATETIME risky? — The same stored value can represent different real moments depending on which zone the reader assumes.
What should recurring local events store besides UTC? — An IANA zone name (e.g. "Europe/London"), so daylight saving is handled correctly.
Where does UTC-to-local conversion belong? — At the presentation layer, not in storage or comparison logic.