How Different Databases Handle Time and Time Zones

Why database time zone handling breaks when DST rules change

A column that stores an instant answers one question: "when did this happen?" A column that stores a wall-clock date and time answers a different one: "what did the clock on the wall say?" Confuse the two and your scheduling logic breaks the next time a government shifts a DST boundary. The three major databases make different trade-offs. The rule: store instants for past events, store wall-clock times plus an IANA zone name for future events. PostgreSQL and MySQL give you both options. SQLite gives you strings and lets you decide.

Pick MySQL TIMESTAMP for automatic UTC conversion, DATETIME for literal values

MySQL offers two temporal column types that include a time component. They look similar but behave differently.

TIMESTAMP stores a UTC instant internally as a 32-bit integer (seconds since epoch). On read it converts to the session's time_zone setting. On write it converts from the session's time zone back to UTC. Change the session time zone and the same stored value displays as a different wall-clock time. Use this for logging events across servers: every server reads the same instant in its own local time.

DATETIME stores a literal value. "2026-07-20 14:30:00" is stored exactly as that sequence of characters. No time zone conversion happens. Insert that value while the session is in America/New_York and you get that string. Read it later while the session is in Europe/London and you still get "2026-07-20 14:30:00". The database does not interpret the value as a moment in time.

MySQL's TIMESTAMP range is limited: 1970-01-01 to 2038-01-19 (the 32-bit overflow). DATETIME ranges from 1000-01-01 to 9999-12-31. For any application that needs dates before 1970 or after 2038, DATETIME is the only choice.

What to do: Use TIMESTAMP when you need automatic UTC conversion across sessions and your data fits the range. Use DATETIME when you need to store dates outside that range, or when you want to preserve exactly what the user typed, zone and all.

Use PostgreSQL timestamptz for instants, bare timestamp for wall-clock times

PostgreSQL is more explicit. It offers TIMESTAMP WITH TIME ZONE (abbreviate it timestamptz) and TIMESTAMP WITHOUT TIME ZONE (abbreviate it timestamp).

TIMESTAMP WITH TIME ZONE does not store the original time zone. It stores a UTC instant internally as an 8-byte integer microsecond count, and it remembers the original zone offset only long enough to validate the input. On read, it converts to the session's TimeZone setting. Insert 2026-07-20 14:30:00 America/New_York and later query with TimeZone set to Europe/London. You get 2026-07-20 19:30:00+01. The original zone is lost.

TIMESTAMP WITHOUT TIME ZONE stores a literal value: "2026-07-20 14:30:00" with no zone information. No conversion occurs on read or write. The application must decide what that value means.

PostgreSQL's range is 4713 BC to 294276 AD for both types. No 2038 problem.

What to do: Use TIMESTAMP WITH TIME ZONE for instants: event logs, creation moments, any time that represents a fixed point. Use TIMESTAMP WITHOUT TIME ZONE for wall-clock times: appointment start times, store opening hours, recurring events where the local meaning matters more than the universal instant.

Store epoch integers for sorting in SQLite, ISO 8601 text for readability

SQLite has no dedicated temporal type. It stores dates and times as text, as integers (epoch seconds or milliseconds), or as real numbers (Julian day numbers). The datetime() function and date modifiers follow ISO 8601 and support UTC offsets, but the database itself does not enforce, convert, or validate time zones.

SQLite's built-in date functions treat 2026-07-20 14:30:00 as a local time by default. To store UTC, call datetime('now') explicitly. It returns the current UTC time in ISO 8601 format. To convert time zones, write SQL using offset arithmetic or store everything as epoch integers and convert in the application layer.

What to do: Use epoch integers (Unix seconds) for simple sorting and arithmetic, and for dates that fit the signed 32-bit range (1970 to 2038). Use ISO 8601 text for readability and for dates outside that range. Use Julian day numbers only for astronomical calculations.

How each database handles DST transitions

MySQL: During a fall-back DST transition (2:00 AM becomes 1:00 AM), a TIMESTAMP value like 2026-11-01 01:30:00 America/New_York is ambiguous. MySQL resolves the ambiguity by choosing the later instant, the one after the transition. There is no syntax to specify which occurrence you mean.

PostgreSQL: The same ambiguous input raises an error. PostgreSQL will not guess. Specify the offset explicitly: 2026-11-01 01:30:00-04 for EDT (pre-transition) or 2026-11-01 01:30:00-05 for EST (post-transition). This is safer but requires awareness in your application.

SQLite: No DST handling exists. The database stores the string you give it. Insert 2026-11-01 01:30:00 during the ambiguous hour and the database keeps that string. The application must decide what it means.

Should you store database times as epoch integers or formatted text?

Storing moments as epoch integers (seconds or milliseconds since 1970-01-01) versus ISO 8601 text is a trade-off.

Epoch integers are compact (4 or 8 bytes), sortable, and trivial to compare. They work well for logging and time-series data. The downside: debugging requires converting a number into a human-readable date. They also hit the 2038 problem in 32-bit systems.

ISO 8601 text is human-readable, self-documenting, and can represent dates outside the Unix epoch range. It takes more storage (typically 20 to 30 bytes for a full entry with offset) and sorts and compares slower in the database.

For most applications, the storage difference is irrelevant. Use epoch integers when you have billions of rows and need fast range queries. Use ISO 8601 text when you need to read the database directly or interoperate with systems that expect human-readable dates.

Migration pitfalls when changing database systems

Moving from MySQL to PostgreSQL is the most common migration. Watch these traps:

• MySQL's TIMESTAMP with session time zone America/New_York stores UTC internally. PostgreSQL's timestamptz also stores UTC. Direct migration works if you preserve the session time zone.
• MySQL's DATETIME stores a literal. PostgreSQL's TIMESTAMP WITHOUT TIME ZONE also stores a literal. Direct migration works: the value is the same string.
• MySQL's TIMESTAMP range ends at 2038. PostgreSQL's timestamptz has no such limit. Any TIMESTAMP values near the boundary will fail in PostgreSQL if they were stored as integers outside the valid range.
• SQLite stores everything as text. Migrating to MySQL or PostgreSQL requires parsing each string and assigning a column type. Text without offsets become DATETIME or TIMESTAMP WITHOUT TIME ZONE. Text with offsets become TIMESTAMP or timestamptz.

One nuance causes constant confusion: PostgreSQL discards the original zone after validating the input. You cannot later ask "what zone was this originally entered in?"

How to choose a database time strategy for a new project

Start with these rules. They cover most use cases.

For new projects on PostgreSQL, make timestamptz your default. Only reach for TIMESTAMP WITHOUT TIME ZONE when you have a clear wall-clock use case.

For new projects on MySQL, make DATETIME your default. Switch to TIMESTAMP only when you need automatic time zone conversion and your data fits the 1970 to 2038 range.

For new projects on SQLite, store epoch milliseconds as an integer if you control the application code. Store ISO 8601 text if you need to inspect the database manually.