Storing dates in databases
DATE, TIME, TIMESTAMP and TIMESTAMPTZ, why epoch integers lose meaning, the store-in-UTC convention, index-friendly range queries, and per-dialect traps.
Pick the type that matches the concept
| Concept | PostgreSQL type | MySQL type | Note |
|---|---|---|---|
| An instant | TIMESTAMPTZ | TIMESTAMP (stored UTC) | Always store an instant in UTC |
| Local wall time, no zone | TIMESTAMP | DATETIME | A meeting time not yet pinned to a zone |
| Calendar date only | DATE | DATE | Birthdays, billing days |
| Time of day only | TIME | TIME | Opening hours |
| Interval | INTERVAL | Not native | Careful with month arithmetic |
| Epoch integer | BIGINT | BIGINT | Loses timezone and calendar semantics |
-- PostgreSQL: TIMESTAMPTZ stores an instant and renders in the session zone
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
occurred_at TIMESTAMPTZ NOT NULL,
event_date DATE NOT NULL,
duration INTERVAL
);
SET TIME ZONE 'UTC'; -- make the session deterministic
INSERT INTO events (occurred_at, event_date, duration)
VALUES ('2026-09-18T10:30:00+02:00', '2026-09-18', INTERVAL '90 minutes');
-- the same instant, rendered differently by session zone
SET TIME ZONE 'Europe/Paris';
SELECT occurred_at FROM events; -- 2026-09-18 10:30:00+02The name TIMESTAMP WITH TIME ZONE is misleading: PostgreSQL does not store a zone. It stores a UTC instant and converts for display, which is exactly what you want.
Why naive timestamps cause tickets
-- DANGER: a naive column silently accepts any local time
CREATE TABLE shipments (shipped_at TIMESTAMP); -- no zone
INSERT INTO shipments VALUES ('2026-09-18 10:30:00'); -- whose 10:30?
-- an application in Tokyo and one in Berlin now write different instants
-- into the same column, and nothing detects the difference.- A naive column has no way to reject a wrong-zone write.
- Daylight saving means a naive local time can be ambiguous or nonexistent.
- Aggregating by day on a naive column groups by the writer's local day, not a chosen business day.
- Use naive types only for values that genuinely have no zone, such as a user's stated birth time.
Ranges, indexes and per-dialect traps
-- half-open range: includes the whole day, excludes the next day's midnight
SELECT count(*) FROM events
WHERE occurred_at >= '2026-09-18T00:00:00Z'
AND occurred_at < '2026-09-19T00:00:00Z';
-- a function on the column defeats the index
WHERE date(occurred_at) = DATE '2026-09-18' -- no index use
-- time-bucket aggregation
SELECT date_trunc('hour', occurred_at AT TIME ZONE 'UTC') AS bucket, count(*)
FROM events GROUP BY 1 ORDER BY 1;| Trap | Dialect | Detail |
|---|---|---|
NOW() vs CURRENT_TIMESTAMP | PostgreSQL | Both are transaction time, not statement time, inside a transaction |
| Timestamp range limited to 2038 | MySQL TIMESTAMP | Use DATETIME to escape the 32-bit limit |
| Implicit zone conversion | MySQL | The server zone affects interpretation of string literals |
| Fractional seconds truncated | SQL Server DATETIME | Use DATETIME2 for precision |
| Epoch unit confusion | SQLite | No date type; seconds or milliseconds must be consistent by convention |
⚠️
Set the database session time zone explicitly in your connection setup. If application code and the session disagree, a
TIMESTAMPTZ column can appear to shift by hours between environments with the same data.FAQ
Should I store epoch integers instead?
Rarely. They save a few bytes, cost you all calendar operations, and lose the ability to reason about a value without external context. Native timestamp types are almost always better.
How do I store a user's time zone?
As a text column holding an IANA name such as
Europe/Paris, never an offset like +02:00. Offsets change twice a year; zone names do not.Related
Parsing and formatting dates safely The 2038 problem and 32-bit time
Last refreshed 2026-09-18.