Storing Dates in Postgres & MySQL

Once you've decided not to store dates as free text (see why that breaks things), the next decision is which native type to reach for — and the three obvious options in Postgres and MySQL behave differently enough that picking the wrong one causes its own quiet bugs later.

DATE: for things that don't have a time

A birthday, a due date, a holiday — anything that's genuinely just a calendar day, with no meaningful hour attached, belongs in a DATE column. It stores year, month, and day only. The temptation to use a full timestamp "just in case" usually backfires: a timestamp implies a specific instant, and specific instants require a timezone to mean anything, which a birthday simply doesn't have.

TIMESTAMP vs. TIMESTAMPTZ: the distinction that actually matters

This is where the two databases diverge in ways worth knowing explicitly. In Postgres, TIMESTAMP stores a date and time with no timezone attached — it's a label, not an instant, and means whatever the reader assumes it means. TIMESTAMPTZ stores the instant itself: internally it's kept in UTC, and Postgres converts to whatever session timezone is active on the way out. For anything representing a real moment — when a user signed up, when an order was placed, when a log line was written — TIMESTAMPTZ is almost always the right choice, because it survives being read from a different timezone without silently changing meaning.

MySQL's DATETIME and TIMESTAMP split the same way, but MySQL's TIMESTAMP additionally has a much smaller range (it runs out in 2038 — the same underlying 32-bit boundary covered in the Year 2038 Problem) and converts based on the server's configured timezone, which can shift under you if that setting ever changes. For most application data going forward, DATETIME stored in UTC, with conversion handled in the application layer, sidesteps both issues.

The pattern that holds up

Store instants in UTC, in a timezone-aware type where the database supports one. Store calendar days as calendar days, with no time or timezone attached at all. Convert to a local timezone only at the point of display, using the viewer's actual timezone rather than the server's. This is a small amount of upfront discipline that prevents an entire category of "why does this date look different depending on who's looking at it" bug reports later.