Storing Timestamps in Databases

Date storage decisions are hard to reverse — by the time a problem appears you have millions of rows of ambiguous data. The choices worth getting right up front are few.

The default answer

Store the instant in UTC. Convert at display time. Keep a separate zone column when the user’s local wall-clock time matters.

Everything below is detail on how each engine lets you do that.

PostgreSQL

Use TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) for instants.

The name is misleading: TIMESTAMPTZ does not store a zone. It converts the input to UTC on write and renders it in the session’s TimeZone on read. That is exactly what you want — one canonical instant, displayed appropriately.

TIMESTAMP (without time zone) stores the literal wall-clock fields with no zone at all. It is not UTC; it is zoneless. Two rows written by servers in different zones are not comparable.

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

SET TimeZone = 'UTC';   -- make session output predictable

Both types are 8 bytes with microsecond resolution and no 2038 issue.

For a future local event, store the components:

CREATE TABLE appointments (
  local_start timestamp NOT NULL,      -- what the user typed
  time_zone   text      NOT NULL,      -- 'America/New_York'
  starts_at   timestamptz GENERATED ALWAYS AS
              (local_start AT TIME ZONE time_zone) STORED
);

MySQL and MariaDB

This is where the 2038 problem actually lives in web applications.

Type	Range	Zone behaviour	2038 safe
TIMESTAMP	1970–2038	Converts to UTC on write, back to session zone on read	No
DATETIME	1000–9999	Stored literally, no conversion	Yes
BIGINT	huge	Whatever you decide	Yes

TIMESTAMP is 32-bit and overflows on 19 January 2038. It was the conventional default in a great deal of older schema advice, which is why so many production databases are quietly exposed. See the Year 2038 problem.

The recommendation: use DATETIME and write UTC values into it yourself.

CREATE TABLE events (
  occurred_at DATETIME(6) NOT NULL   -- microsecond precision, store UTC
);

SET time_zone = '+00:00';   -- pin the session

Be aware that FROM_UNIXTIME() and UNIX_TIMESTAMP() both apply the session time_zone. Two connections with different session zones get different answers from the same query — pin it explicitly in your connection setup.

SQLite

SQLite has no date type. You choose the representation:

• INTEGER — Unix seconds. Compact, fast, sorts correctly. The usual choice.
• TEXT — ISO 8601 strings. Human-readable and still sorts correctly, since ISO ordering is lexical. Use YYYY-MM-DD HH:MM:SS in UTC.
• REAL — Julian day numbers. Rarely worth it.

CREATE TABLE events (occurred_at INTEGER NOT NULL);   -- Unix seconds, UTC
SELECT datetime(occurred_at, 'unixepoch') FROM events;

Being explicit about the unit in the column name (occurred_at_ms) is especially valuable here, since nothing in the schema records it.

SQL Server

Use datetimeoffset when you need the original offset preserved, or datetime2 for UTC instants.

Avoid the legacy datetime type: its range starts in 1753 and its resolution is 3.33 milliseconds, so values are silently rounded.

CREATE TABLE events (occurred_at datetime2(6) NOT NULL);   -- store UTC

Storing epochs as integers

Perfectly reasonable, and sometimes preferable — it is unambiguous across every engine and language, and immune to session-zone surprises.

Two rules:

1. Use BIGINT, never INT. A 32-bit integer column reintroduces the 2038 problem regardless of how good your application types are.
2. Name the unit. expires_at_ms versus expires_at_s. Nothing else in the schema records this, and mixing seconds with milliseconds is the most common date bug there is.

The trade-off is readability: SELECT * FROM events returns a column of opaque integers, and every ad-hoc query needs a conversion function. For tables humans query directly, a native datetime type is usually worth more than the marginal storage saving.

Things that reliably cause pain

Storing local time without a zone. Unrecoverable later. You cannot determine which zone a naive timestamp was written in.

Relying on the server’s local zone. The one guarantee is that it will differ between your laptop, CI and production. Pin every layer to UTC: the OS, the database session, and the application runtime.

Pre-computing future instants from current rules. Zone rules change. Store local time plus IANA zone for future events and resolve at read time — see time zones vs UTC offsets.

Ignoring precision. TIMESTAMP defaults to whole seconds in MySQL; you need DATETIME(6) for microseconds. If you are deduplicating or ordering high-frequency events, second resolution collides constantly.

Letting the ORM decide. Check what your ORM actually emits in the migration. Several default to TIMESTAMP on MySQL, which is the 2038-exposed type.

A short checklist

1. PostgreSQL → timestamptz. MySQL → DATETIME(6). SQLite → INTEGER seconds. SQL Server → datetime2.
2. Never TIMESTAMP on MySQL, never INT for an epoch.
3. Pin every session and process to UTC.
4. Name the unit in integer columns.
5. Store the IANA zone separately for future local events.
6. Decide your precision deliberately, and test with sub-second values.

To audit an existing column, export it and run it through the batch converter — mixed units and 1970 values show up immediately.