Skip to content

PLANETSCALE 2026-04-21 Tier 3

Read original ↗

PlanetScale — Datetimes versus timestamps in MySQL

Summary

Short PlanetScale pedagogical post by Aaron Francis (originally published 2023-06-22, re-surfaced via the 2026-04-21 feed snapshot) canonicalising the five temporal column types MySQL offers for storing dates, times, and combined points in time — DATE, DATETIME, TIMESTAMP, YEAR, TIME — with their storage costs, representable ranges, and semantic trade-offs. The post's central comparison is the DATETIME vs TIMESTAMP choice: both store a date + time to 1-second resolution, but TIMESTAMP is 4 bytes (half the 8-byte DATETIME size) at the cost of a narrow range (1970-01-01 00:00:00 to 2038-01-19 03:14:17 — the canonical Year 2038 problem on signed-32-bit-int Unix seconds) and implicit timezone conversion ("MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)"). A worked example demonstrates the timezone asymmetry: a row inserted as 2029-02-14 08:47 under SET SESSION time_zone = '+00:00' returns verbatim on read, but re-queried under SET SESSION time_zone = '-05:00' yields 2029-02-14 03:47 in the TIMESTAMP column (shifted 5 hours) while the DATETIME column still returns 2029-02-14 08:47. Production guidance: use TIMESTAMP only for application-populated event-time columns (created_at, updated_at, deleted_at, archived_at, posted_at) where the value is always the server-current time and therefore won't exceed 2038 in practice; use DATETIME for any field where the date may be far-future (e.g. user-entered birthdays, planned events beyond 2038) or where you need timezone-neutral storage. DATE (3 bytes, year 1000 to 9999) covers pure-date cases like birthdays; TIME (3 bytes, -838:59:59 to 838:59:59 — ~35 days in either direction to support interval arithmetic as well as wall-clock time-of-day); YEAR (1 byte, 1901 to 2155) is the rare niche. No marketing, no PlanetScale-specific machinery — straight MySQL pedagogy.

Key takeaways

  1. Five temporal column types, each optimised for a different shape of temporal data. Summary table (byte sizes + legal ranges from the post):
Column Data Bytes Min Max
DATE Date only 3 1000-01-01 9999-12-31
DATETIME Date + time 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP Date + time 4 1970-01-01 00:00:00 2038-01-19 03:14:17
YEAR Year only 1 1901 2155
TIME Time only 3 -838:59:59 838:59:59

Schema-design decision tree: date-only → DATE; year-only → YEAR; time-only or duration-interval → TIME; combined date + time → either DATETIME or TIMESTAMP (see takeaways 2–4). (Source: article body, "there are five column types that you can use to store temporal data in MySQL.")

  1. TIMESTAMP is 4 bytes vs DATETIME's 8 bytes — exactly half the storage for combined date+time data. For high-cardinality event-log tables (billions of rows × created_at + updated_at + deleted_at columns), the saving is non-trivial at scale. Canonical schema-design guidance from the post: "Storage space is cheap, but when designing table schemas, we always want to choose the smallest possible column type that fits the full range of our data. When creating your tables, there is no need to be generous!"

  2. TIMESTAMP's narrow range IS the Year 2038 Problem. The 4-byte size comes from MySQL representing TIMESTAMP internally as a signed-32-bit-int count of Unix seconds since 1970-01-01 00:00:00 UTC — the canonical Unix epoch. The 2038-01-19 03:14:17 ceiling is exactly 2^31 - 1 seconds after the epoch, at which point the counter overflows. "The 2038 issue arises in systems that calculate Unix time … These systems use a signed 32-bit integer to store this time, which can only hold integer values between -2^31 and 2^31 - 1." Also known as Y2038, Y2K38, or The Epochalypse. For any TIMESTAMP column that could ever hold a post-2038 value (user-entered future dates, planned-event dates, long-lived contracts, lifetime warranties, birthdays of children born after 2007 who will hit 30+ post-2038), TIMESTAMP is disqualified — use DATETIME (range extends to 9999-12-31 23:59:59).

  3. TIMESTAMP performs implicit timezone conversion; DATETIME does not. From the MySQL 8.0 documentation (quoted in the post): "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time." This makes TIMESTAMP the correct choice for timezone-aware event logging on a UTC-server infrastructure (values land normalised to UTC regardless of client timezone; reads get localised to the client's zone automatically) — but a trap if any production or replica server has a non-UTC timezone: reads shift under the reader's timezone silently. DATETIME is timezone-naïve — the bytes stored are the bytes returned, unchanged, regardless of SET SESSION time_zone = ....

  4. Worked proof of the timezone asymmetry. 

    CREATE TABLE timezone_test (
    `timestamp` TIMESTAMP,
    `datetime` DATETIME
);

SET SESSION time_zone = '+00:00';
INSERT INTO timezone_test VALUES ('2029-02-14 08:47', '2029-02-14 08:47');

SELECT * FROM timezone_test;
-- | timestamp           | datetime            |
-- |---------------------|---------------------|
-- | 2029-02-14 08:47:00 | 2029-02-14 08:47:00 |

SET SESSION time_zone = '-05:00';
SELECT * FROM timezone_test;
-- | timestamp           | datetime            |
-- |---------------------|---------------------|
-- | 2029-02-14 03:47:00 | 2029-02-14 08:47:00 |
    The TIMESTAMP column shifts 5 hours under the new session timezone (stored UTC value 2029-02-14 08:47:00 minus 5 hours = 2029-02-14 03:47:00 at -05:00); the DATETIME column is unchanged. (Source: article body, "Let's change our session timezone to -05:00 now and see what happens.")

  5. Production rule of thumb: use TIMESTAMP for application-populated event-time columns — updated_at, created_at, deleted_at, archived_at, posted_at — because "as long as these columns record the current time at which an event happened, you don't have to worry about the 2038 problem for a long time." Use DATETIME for user-input date fields, future-planned events, anniversaries, or anything that may exceed 2038 — or alternatively validate at the application layer that the input falls in the 1970–2038 window before writing to a TIMESTAMP.

  6. TIME covers wall-clock AND interval storage. Legal range -838:59:59 to 838:59:59 — roughly 35 days in either direction, far wider than the 00:00:00–23:59:59 wall-clock range. This is a deliberate design choice so TIME can store both "a time of day" and "an interval of time" in the same type, which is unusual among databases. Sign support enables subtraction semantics (TIMEDIFF('2029-02-14 08:00', '2029-02-15 08:00') = -24:00:00).

  7. DATE's massive range — year 1000 through 9999, in 3 bytes — makes it effectively unlimited for any real-world application. Use it without hesitation for birthdays, anniversaries, employee start dates, calendar days, any domain where only the date component matters.

  8. YEAR is the least-used type — 1 byte, range 1901 to 2155. "Not widely used, but it serves its purpose exceedingly well when you need it." Outside the supported range, migrate to a signed or unsigned small integer for broader year storage.

Systems / concepts / patterns extracted

  • Systems: MySQL (the relational database whose temporal-type catalogue the post canonicalises).
  • Concepts: MySQL temporal types (the 5-type catalogue DATE / DATETIME / TIMESTAMP / YEAR / TIME with sizes + ranges); DATETIME vs TIMESTAMP in MySQL (the canonical schema-design comparison: 8B vs 4B storage, broad vs narrow range, timezone-naïve vs timezone-converting); [[concepts/ year-2038-problem|Year 2038 Problem]] (Unix-epoch signed-32-bit-int rollover, the structural reason for TIMESTAMP's 2038-01-19 03:14:17 ceiling).
  • Patterns: None. The post is pedagogical reference material — schema-design conventions ("choose the smallest possible column type that fits the full range of your data", "use TIMESTAMP for application-populated event-time columns") are documented in-page on the datetime-vs-timestamp-mysql concept rather than as reusable cross-system patterns.

Operational numbers

  • 5 temporal column types in MySQL: DATE, DATETIME, TIMESTAMP, YEAR, TIME.
  • Storage sizes: YEAR 1 byte; DATE 3 bytes; TIME 3 bytes; TIMESTAMP 4 bytes; DATETIME 8 bytes. 2× storage saving for TIMESTAMP vs DATETIME at the cost of range.
  • TIMESTAMP range: 1970-01-01 00:00:00 to 2038-01-19 03:14:17 — 2^31 - 1 = 2,147,483,647 seconds after the Unix epoch.
  • DATETIME range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 — effectively unlimited for real-world applications.
  • DATE range: 1000-01-01 to 9999-12-31 — same broad range as DATETIME without the time component.
  • YEAR range: 1901 to 2155 (255 years).
  • TIME range: -838:59:59 to 838:59:59 — approximately ±35 days in either direction, supporting both wall-clock time and interval arithmetic.
  • Y2038 overflow: 2^31 - 1 seconds = January 19, 2038 at 03:14:07 UTC.

Caveats

  • Pedagogy voice, not a production retrospective — no incident narrative, no failure modes at scale, no PlanetScale-specific temporal-type handling disclosed. Same class as the already-ingested sources/2026-04-21-planetscale-character-sets-and-collations-in-mysql post (Aaron Francis pedagogy on MySQL schema primitives).
  • No fractional-second precision discussion — MySQL 5.6.4+ supports DATETIME(6) / TIMESTAMP(6) for microsecond precision at the cost of extra storage (0 to +3 bytes depending on the precision chosen). The post ignores this entirely — all examples use 1-second resolution.
  • No NOW() / CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP default-expression coverage — the updated_at pattern cited as TIMESTAMP's canonical use case typically relies on ON UPDATE CURRENT_TIMESTAMP for auto-update semantics, but the post doesn't describe the mechanism.
  • No index-size implications — DATETIME's 8 bytes vs TIMESTAMP's 4 bytes affects B-tree index page density; at high cardinality (billion-row event tables), this materially changes the InnoDB buffer pool footprint of the index. The post doesn't quantify.
  • No migration guidance — converting TIMESTAMP → DATETIME on a production table is an ALTER TABLE that rewrites the table; the guidance that TIMESTAMP might be the wrong choice for far-future dates doesn't touch how to migrate an existing TIMESTAMP column safely.
  • Server-timezone configuration elided — the post notes "by default, the current time zone for each connection is the server's time" but doesn't walk through how to set time_zone = 'UTC' at the server level (my.cnf, SET GLOBAL time_zone, or OS-level TZ=UTC mysqld) to avoid the silent-shift trap in replicated / multi-region deployments.
  • No Y2038 solution framing — the post quips "surely, by then, we'll have a solution for it. (Surely, right?)" but doesn't mention that MySQL has not yet committed to extending TIMESTAMP beyond 32-bit Unix seconds, nor that 64-bit Unix time (time64_t) is the standard Linux / glibc migration path for the broader Y2038 problem. TIMESTAMP remains a walking bug that will need an explicit migration before 2038.
  • PostgreSQL comparison omitted — the post is MySQL-only; doesn't note that Postgres's timestamp (without time zone) maps to DATETIME, timestamptz (with time zone) stores UTC internally with a timezone-aware read semantic similar to but not identical to MySQL's TIMESTAMP, and Postgres's interval type is a dedicated duration type avoiding MySQL TIME's overloaded role.
  • No discussion of how MySQL handles pre-1970 values on TIMESTAMP — the TIMESTAMP range begins at 1970-01-01 00:00:00; historical dates (birthdays for anyone born before 1970, historical events) cannot be stored in TIMESTAMP at all — must use DATE or DATETIME.

Source

Last updated · 347 distilled / 1,201 read