Storing Dates In Databases

Last reviewed on 25 April 2026.

The choice between date, timestamp, and timestamp with time zone is one of those decisions that looks trivial at the schema-design stage and becomes load-bearing the moment a system has users in more than one country. Get it wrong and reports drift across midnight, audit logs disagree about which day an event happened, and a "yesterday" filter quietly excludes a few hours of the right day.

This page is a working guide to picking the right column type for the data you have, and to handling time zones consistently when reading and writing it.

Three kinds of value, three kinds of column

Almost every datetime in a typical application falls into one of three categories. Picking the right column type starts with putting your value into the right category.

1. A calendar date with no time of day

A birthday. A holiday. A contract effective date. The value is a day in someone's calendar; it has no hour, no minute, and no zone. The right column type is the database's pure date type — DATE in PostgreSQL, MySQL, SQL Server, Oracle, and SQLite (in TEXT form, conventionally as YYYY-MM-DD).

The temptation is to "upgrade" this to a timestamp because timestamps feel safer. They are not. A timestamp adds an hour, which then needs a zone, which then needs an answer to "in which zone was this date intended?" — a question that has no good answer for a birthday. Keep date values in date columns.

2. An instant in time

A row was created. A payment cleared. A user clicked a button. These are instants: a specific moment that everyone in the world experiences simultaneously, even if their clocks read different things. The right column type is the database's timestamp with time zone — TIMESTAMPTZ in PostgreSQL, TIMESTAMP WITH TIME ZONE in standard SQL, DATETIMEOFFSET in SQL Server. MySQL's TIMESTAMP behaves similarly when the session zone is UTC.

Despite the name, TIMESTAMPTZ in PostgreSQL does not actually store a zone. It stores a UTC instant; the "with time zone" part means the engine converts on the way in and on the way out. The practical effect is that two clients in different zones write the same row and read back the same instant, even if the textual representation differs. That is exactly what you want for instants.

3. A wall-clock time at a known location

A flight departs at 09:30 from Frankfurt. A meeting is set for Tuesday at 14:00 in New York. These are not instants — they are local times that should follow the destination's daylight-saving rules even if the rules change between when you save the row and when the event happens. Storing this as a UTC timestamp can produce the wrong wall-clock time after a DST transition.

The pragmatic shape is two columns: a wall-clock TIMESTAMP WITHOUT TIME ZONE (or DATETIME) and a separate column holding the IANA zone identifier ("Europe/Frankfurt", "America/New_York"). Compute the instant on read using a current zone database. Some application frameworks model this as a single composite type; the underlying storage is the same idea.

The default that fits most apps: store instants in UTC

For event timestamps — created_at, updated_at, last_seen, audit log entries — store the instant in UTC and let the database do the conversion. The time zones page covers why UTC is the canonical reference. From the database side the recipe is short:

• Use the engine's timestamp-with-time-zone type for instants.
• Set the database, application, and connection-level session zones to UTC. Do not "rely on the server's local zone".
• Convert to a user's zone at the edge — when rendering to a web page, generating a PDF, or formatting a notification.

Doing this consistently is more important than which exact type name you use. A schema where half the timestamps are UTC and half are server-local is the worst of both worlds.

Engine-by-engine notes

PostgreSQL

DATE for calendar dates. TIMESTAMPTZ for instants. TIMESTAMP (without time zone) for wall-clock times paired with a zone column. PostgreSQL stores all TIMESTAMPTZ values as UTC internally; SET TIME ZONE changes only how the value is rendered, not what is stored. Do not use TIMESTAMP for instants; the zone is dropped at write and the value will be misinterpreted on a server with a different default zone.

MySQL and MariaDB

DATE for calendar dates. TIMESTAMP stores the instant as UTC under the hood and converts using the connection's zone — useful, but range-limited (in classic MySQL, around 1970 to early 2038). DATETIME stores wall-clock values without conversion and has a wider range, but it is your job to track the zone separately. For new schemas, prefer TIMESTAMP for instants until the 2038 limitation is a concrete problem; pair DATETIME with a zone column when you need wall-clock semantics or values outside the timestamp range.

SQL Server

DATE for calendar dates. DATETIMEOFFSET for instants — it stores the offset, which is enough to recover the UTC moment. DATETIME2 for wall-clock times paired with a separate zone column. The legacy DATETIME type has a smaller range and lower precision; new schemas should default to DATETIME2 or DATETIMEOFFSET.

SQLite

SQLite has no first-class date types. The convention is to store dates as ISO 8601 text (YYYY-MM-DD for dates, YYYY-MM-DDTHH:MM:SSZ for instants in UTC). Lexicographic ordering of ISO 8601 strings is chronological, which keeps range queries and sorting honest. Use Unix epoch integers if you specifically need fixed-width binary timestamps.

Comparison at a glance

Indexing and query patterns

Range queries on instants — "all events in the last 24 hours", "everything from Q1" — are the bread and butter of analytical queries. A few patterns keep them fast:

• Index the raw column, not a function of it. An index on created_at can serve WHERE created_at >= $1 AND created_at < $2. An index on DATE(created_at) only serves queries that wrap the column in the same function. Most engines support functional or expression indexes when you really need them, but add the function deliberately, not by accident.
• Use half-open intervals. Prefer created_at >= '2025-04-01' AND created_at < '2025-05-01' to BETWEEN with end-inclusive dates. Half-open intervals avoid the "did you mean midnight on the 30th or the 1st?" trap.
• Be explicit about the zone in the query. "Events in April" means different things in Tokyo and Los Angeles. If reporting must align with a specific business zone, convert at query time and document the choice.
• Partition by month or day for very large tables. Time-series workloads benefit from partitioning on the date or instant column; pruning kicks in for range queries automatically.

Common mistakes

• Storing dates as strings in non-ISO formats. A VARCHAR column with "4/3/25" in it sorts wrong, joins wrong, and migrates badly. If you have to store as text, use ISO 8601 only.
• Letting the connection's session zone drift. A misconfigured client that connects with the operating-system local zone instead of UTC can write rows that look right today and read back wrong tomorrow if the server is moved or the daylight rule changes.
• Trusting application defaults. Many ORMs serialise Date objects using the current process zone. The fix is to set both the database session and the application zone to UTC explicitly, then convert at the edge.
• Using float seconds for timestamps. Doubles lose precision near the millisecond level for modern timestamps and create rounding errors in joins. Stick to integer epoch values or native timestamp types.
• Adding 24 hours to advance one day. On a DST transition day, that gives you the wrong wall-clock time. Use a date arithmetic function in the database or a date library in the application.

Migration: turning a mistake into the right type

If you inherit a schema where instants live in TIMESTAMP WITHOUT TIME ZONE in server-local zone, the migration usually has three steps:

1. Add a new TIMESTAMPTZ column.
2. Backfill it from the old column, telling the database which zone the existing values were in (old_col AT TIME ZONE 'America/Los_Angeles' in PostgreSQL, for example).
3. Switch reads and writes to the new column, then drop the old one in a follow-up migration once nothing depends on it.

Do the backfill in a transaction with explicit zone conversion. Eyeballing it during the cutover is how DST transitions get permanently misrecorded.

Related reading

• Time zones — the conceptual foundation for picking and storing zones.
• Date parsing pitfalls — what to validate before a value reaches the database.
• Date and time API reference — language-by-language helpers for converting between application objects and database types.
• ISO 8601 and other standards — the textual formats your text columns should follow.