Learning Objectives
- Distinguish between timezone-naive DATETIME and timezone-aware TIMESTAMP WITH TIME ZONE
- Understand how MySQL DATETIME and PostgreSQL TIMESTAMPTZ differ in behavior and use cases
- Recognize how changing the server timezone retroactively alters TIMESTAMP column values
Timezone-Naive vs Timezone-Aware
The most dangerous misconception in date/time handling is treating a timezone-naive type as if it carries timezone information. In SQL, the two camps are:
| Behavior | Types |
|---|---|
| Timezone-naive (stores literal wall-clock time) | MySQL DATETIME, PostgreSQL TIMESTAMP WITHOUT TIME ZONE |
| Timezone-aware (stores a point in time, normalizes to UTC) | MySQL TIMESTAMP, PostgreSQL TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) |
A timezone-naive column stores exactly what you write. Insert '2024-06-15 10:00:00' and you get '2024-06-15 10:00:00' back, regardless of where on Earth the database server lives. There is no conversion on write and no conversion on read.
A timezone-aware column stores a point in absolute time. PostgreSQL's TIMESTAMPTZ converts the input to UTC, stores the UTC value, and then converts it back to the session timezone on retrieval. The database always preserves the "moment in time."
MySQL vs PostgreSQL: A Critical Difference
MySQL adds a significant twist: its TIMESTAMP type is timezone-aware (converts to UTC on store), but its range is limited to 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC — exactly the 32-bit epoch boundary. MySQL's DATETIME has a wider range (1000–9999 AD) but is timezone-naive.
MySQL TIMESTAMP: timezone-aware, but range-limited (expires 2038)
MySQL DATETIME: wide range, but timezone-naive (wall-clock values only)
PostgreSQL TIMESTAMP: timezone-naive, wide range
PostgreSQL TIMESTAMPTZ: timezone-aware, wide range (the recommended type)
When testing a MySQL application that migrates to PostgreSQL, verify that all DATETIME columns were mapped to TIMESTAMPTZ, not TIMESTAMP. Mapping to TIMESTAMP (without time zone) in PostgreSQL silently changes semantics.
The Server Timezone Change Problem
Here is the critical bug that catches teams off guard:
- Your application stores
created_atasTIMESTAMP WITHOUT TIME ZONE(timezone-naive). - All values were inserted while the server ran in UTC — values read as
'2024-06-15 10:00:00'. - The DBA changes the server timezone to
Europe/Kyiv(+02:00). - Existing rows still read as
'2024-06-15 10:00:00'— unchanged (naive type, no conversion). - New inserts now store
'2024-06-15 12:00:00'for the same logical "10:00 UTC" moment. - The column is now semantically inconsistent: old rows are UTC, new rows are Kyiv time.
With TIMESTAMPTZ, the same scenario is safe: PostgreSQL stores UTC and reconverts on read, so all existing rows automatically display correctly in the new session timezone with no data inconsistency.
Testing Implications
Test cases every QA engineer should run for timestamp-sensitive features:
- Timezone change test: Record a timestamp, change the application/DB server timezone, re-read the timestamp. Does it still represent the same moment in time?
- Cross-timezone comparison: Insert a record from a UTC client and query it from a UTC+5:30 client. Do range queries return the expected rows?
- MySQL 2038 boundary: Insert
2038-01-19 03:14:07into a MySQLTIMESTAMPcolumn. Verify the row is rejected or truncated (depending onsql_mode), not silently stored as0000-00-00 00:00:00. - NULL behavior: Insert NULL into a
TIMESTAMPcolumn with aDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP— the behavior differs between MySQL and PostgreSQL.
Understanding these semantic differences is the difference between a QA engineer who finds timezone bugs before production and one who files them as "cannot reproduce."