MySQL DATETIME vs PostgreSQL timestamptz: A Migration Map

Temporal columns are the sneakiest part of a MySQL-to-PostgreSQL migration. The data types look interchangeable, the values copy across without errors, and then three weeks after cutover someone notices that every event before the migration appears shifted by the office timezone. I have done this migration enough times to have a checklist, and this article is that checklist with the reasoning attached.

The core problem is that MySQL and PostgreSQL both offer a timezone-naive type and a timezone-aware type, but they draw the line in different places and convert at different moments. Getting the mapping right requires knowing what each of the four types actually stores.

MySQL's two temporal types are less similar than they look

MySQL DATETIME is a wall-clock value: it stores exactly the digits you gave it, has no timezone awareness at all, and covers years 1000 through 9999. What you insert is what everyone reads, regardless of who is reading from where. Since 8.0.19 you can include an offset in the literal, but MySQL converts it and stores a plain wall-clock value; the offset is not retained.

MySQL TIMESTAMP is a different animal. It stores seconds since the Unix epoch in UTC, and converts on the way in and out using the session time_zone variable. Two clients with different session timezones will read different strings from the same stored value. TIMESTAMP also carries the legacy automatic-initialization behavior: depending on version and the explicit_defaults_for_timestamp setting, the first TIMESTAMP column in a table may get implicit DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP semantics nobody asked for.

And then there is the ceiling. Because TIMESTAMP is a 32-bit epoch value at heart, its documented range ends at 2038-01-19 03:14:07 UTC. MySQL has been extending internals over the 8.x series, but for the versions most teams run, a TIMESTAMP column cannot represent 2039. Teams discovered this the practical way when storing certificate expiry dates and 20-year contract end dates; the common workaround was switching those columns to DATETIME and handling UTC discipline in the application.

PostgreSQL's model: same split, cleaner rules

PostgreSQL also has two types, and the naming is the first trap: timestamp (officially timestamp without time zone) and timestamptz (timestamp with time zone). Despite the name, timestamptz does not store a timezone. Both types are 8 bytes, store microseconds since 2000-01-01 internally, and cover 4713 BC to 294276 AD, so the 2038 problem simply does not exist on this side.

The difference is entirely about conversion. timestamp is the analogue of MySQL DATETIME: a wall-clock value, stored and returned verbatim. timestamptz is the analogue of MySQL TIMESTAMP done consistently: input is interpreted using the offset in the literal, or the session TimeZone setting if none is given, normalized to UTC for storage, and rendered in the session TimeZone on output.

-- PostgreSQL: one stored instant, rendered per session
SET TimeZone = 'UTC';
SELECT TIMESTAMPTZ '2026-07-07 09:00:00+02:00';
-- 2026-07-07 07:00:00+00

SET TimeZone = 'America/New_York';
SELECT TIMESTAMPTZ '2026-07-07 09:00:00+02:00';
-- 2026-07-07 03:00:00-04

The practitioner consensus on the PostgreSQL side is strong: use timestamptz for instants, which is almost everything an application records, and reserve timestamp for genuine wall-clock semantics like "the store opens at 09:00 local time." The full argument, including the AT TIME ZONE conversion rules that trip people up, is in timestamp vs timestamptz.

The mapping rules I actually use

Rule one: MySQL TIMESTAMP maps to timestamptz. Both represent UTC instants with session-based rendering; this is the one genuinely mechanical mapping. As a bonus, the move retires the 2038 ceiling and the implicit-default weirdness in one step. Recreate any ON UPDATE CURRENT_TIMESTAMP behavior explicitly with a trigger or application code, because PostgreSQL has no column-level equivalent.

Rule two: MySQL DATETIME maps to whatever the column meant, and only a human can answer that. In practice most DATETIME columns hold UTC instants by team convention, because engineers learned to distrust TIMESTAMP and did the conversion in the application. Those columns should become timestamptz, with an explicit conversion during transfer:

-- PostgreSQL: interpreting migrated DATETIME values as UTC
ALTER TABLE events
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

If the convention was server-local time rather than UTC, substitute the correct region name, and be honest about eras: I have migrated tables where rows before a certain deploy were US/Eastern and rows after it were UTC, and the only fix was a dated CASE expression in the USING clause. Columns that are genuinely wall-clock, like scheduled local appointment times, should stay timestamp without time zone, ideally next to a column recording the relevant IANA timezone name.

Rule three: audit the dump pipeline itself. mysqldump converts TIMESTAMP columns to UTC by default via its tz-utc behavior, but ad hoc export queries inherit whatever session time_zone the export connection had. A surprising number of migration bugs are not mapping bugs; they are an export connection defaulting to the server's local timezone.

Traps that survive the type mapping

Zero dates are the classic one. MySQL under permissive SQL modes accepted 0000-00-00 00:00:00 as a placeholder, and old tables are full of them. PostgreSQL rejects the value outright, so decide up front whether zero dates become NULL, a sentinel, or a data-quality ticket. Finding them before the transfer beats finding them as constraint violations halfway through.

Fractional seconds need a look. MySQL temporal types default to zero fractional digits and truncate unless the column was declared DATETIME(6); PostgreSQL keeps microseconds by default. Values round-trip fine, but comparisons between old truncated data and new full-precision data can produce off-by-under-a-second surprises in deduplication logic.

Range checks matter in the other direction too: DATETIME accepts year 9999 while timestamptz happily exceeds it, but if anything ever writes data back or compares against MySQL-era bounds, keep the sentinel values consistent. And test daylight-saving boundaries explicitly. Ambiguous local times, like 01:30 on a fall-back night, are resolved by engine-specific rules, and any DATETIME column holding local wall-clock history contains hours that are genuinely ambiguous no matter what you do.

Set the session rules before the app goes live

After cutover, standardize the PostgreSQL side: set TimeZone to UTC in the database or role configuration, make drivers connect with an explicit timezone rather than inheriting from the host, and convert to local time at the presentation edge only. Most modern drivers hand timestamptz to the application as a proper instant type, which quietly removes the class of bugs where a string was parsed in the wrong zone.

The one habit worth importing from your MySQL years is suspicion of session state. On MySQL you learned to check time_zone before trusting a TIMESTAMP reading; on PostgreSQL, the same discipline applies to TimeZone when eyeballing timestamptz values in psql during an incident.

Finally, verify the conversion with arithmetic rather than eyeballs. A checksum query that buckets rows per day and compares counts and min and max values between the MySQL source and the PostgreSQL target will catch a systematic offset immediately, because a timezone mistake shifts rows across day boundaries in predictable numbers. Run it against a full copy of production data before cutover, and again against the live system after, because the export path and the ongoing replication path can have different session settings and different bugs. Ten minutes of paranoia here is cheaper than re-deriving event history from application logs later.

After the cutover: MonPG on the PostgreSQL side

MonPG monitors PostgreSQL only, so its job starts once your data lands there, and temporal migrations create exactly the workload changes it exists to catch. Type conversions with USING clauses rewrite whole tables, which means vacuum debt and bloat to watch in the following days. Rewritten queries that previously leaned on MySQL's index behavior need their PostgreSQL plans confirmed, and a created_at range scan that stops using an index is the kind of regression that hides until month-end reporting.

With PostgreSQL monitoring in place, you get pg_stat_statements history to compare query families before and after each migration wave, vacuum and bloat signals for the rewritten tables, and lock visibility while long backfills run. The type mapping is a design decision; verifying it held up under production traffic is an evidence problem, and evidence is the part you can automate.