Choose MySQL DATETIME or TIMESTAMP with time zone and range in mind

An appointment saved as 09:00 reappears as 10:00 after a server move. A subscription expiry in 2042 cannot fit in a TIMESTAMP. The choice between MySQL DATETIME and TIMESTAMP is not about which name sounds more precise. It is about whether the value represents an instant, a wall-clock label, or a date independent of time.

Classify the business meaning first

Value	Meaning	Typical model
Payment received	One instant on the global timeline	UTC instant, often TIMESTAMP(6) or UTC DATETIME(6)
Shop opens at 09:00 Lagos time	Recurring civil time plus zone rules	Local time + IANA zone identifier
Customer birthday	Calendar date	DATE
Historic event in 1945	Out of TIMESTAMP range	DATETIME plus explicit interpretation
Subscription expires in 2042	Future instant beyond 2038	UTC DATETIME or other deliberate representation

Know the range before migrating data

MySQL 8.4 documents DATETIME from year 1000 through 9999. TIMESTAMP is constrained to approximately 1970-01-01 UTC through 2038-01-19 UTC. Fractional seconds up to six digits are available for both.

CREATE TABLE temporal_probe (
    observed_at TIMESTAMP(6) NOT NULL,
    scheduled_for DATETIME(6) NOT NULL
);

Do not select TIMESTAMP for contracts, certificates, leases or long-lived schedules without checking the maximum business horizon. Strict SQL mode should turn out-of-range input into an error rather than a coerced zero-like value.

Understand TIMESTAMP session conversion

SET time_zone = '+00:00';
INSERT INTO temporal_probe
VALUES ('2026-07-27 12:00:00.000000',
        '2026-07-27 12:00:00.000000');

SET time_zone = '+01:00';
SELECT observed_at, scheduled_for
FROM temporal_probe;

For TIMESTAMP, MySQL converts from the session time zone to UTC for storage and converts from UTC to the session zone on retrieval. DATETIME values are not changed by the session time zone. The same stored row can therefore display a different TIMESTAMP clock value in two sessions while DATETIME remains the literal fields inserted.

Make every connection’s time zone explicit

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
SET SESSION time_zone = '+00:00';

Configure the application pool so every new connection sets and verifies UTC. Do not assume a server host configured for UTC guarantees the session setting, especially across proxies, replicas and managed services. Named zones require populated MySQL time-zone tables.

SELECT @@system_time_zone,
       @@global.time_zone,
       @@session.time_zone,
       NOW(),
       UTC_TIMESTAMP();

Do not mistake an offset for a time-zone identity

2026-10-25 01:30:00+01:00 carries an offset for one instant. It does not preserve the rule set “Europe/London,” which can change offset seasonally and politically. If future events must stay at local wall time, store:

CREATE TABLE appointments (
    appointment_id bigint PRIMARY KEY,
    local_start DATETIME(6) NOT NULL,
    time_zone_id varchar(64) NOT NULL,
    resolved_start_utc DATETIME(6) NULL,
    CONSTRAINT CK_appointments_zone
      CHECK (time_zone_id <> '')
);

Resolve with a maintained IANA time-zone database, define behavior for nonexistent and repeated local times, and recompute future derived instants when rules change. MySQL DATETIME does not carry a zone ID internally.

Use automatic initialization explicitly

CREATE TABLE orders (
    order_id bigint PRIMARY KEY,
    created_at TIMESTAMP(6) NOT NULL
      DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) NOT NULL
      DEFAULT CURRENT_TIMESTAMP(6)
      ON UPDATE CURRENT_TIMESTAMP(6)
);

Modern MySQL allows automatic initialization and update behavior for TIMESTAMP and DATETIME. State it in schema source rather than relying on legacy implicit rules. Decide whether every update—including a harmless retry—should alter updated_at; application-managed versioning may better represent business change.

Use UTC DATETIME deliberately when range matters

CREATE TABLE subscriptions (
    subscription_id bigint PRIMARY KEY,
    expires_at_utc DATETIME(6) NOT NULL,
    CHECK (expires_at_utc >= '2000-01-01 00:00:00')
);

The _utc suffix is part of the contract because DATETIME itself does not enforce UTC. Normalize in the application, reject offsets that were not converted, and return an ISO 8601 value with Z at API boundaries.

Preserve fractional precision end to end

CREATE TABLE events (
    event_id bigint PRIMARY KEY,
    occurred_at TIMESTAMP(6) NOT NULL,
    UNIQUE KEY uq_event_time (occurred_at, event_id)
);

Precision in the column does not help if the application driver rounds to milliseconds or seconds. Test serialization, parameter binding, replication and API output. A timestamp is usually not a safe unique identifier; concurrent events can share it, so add a stable key.

Test daylight-saving gaps and overlaps

SELECT
  CONVERT_TZ('2026-03-29 01:30:00',
             'Europe/London', 'UTC') AS spring_case,
  CONVERT_TZ('2026-10-25 01:30:00',
             'Europe/London', 'UTC') AS autumn_case;

The spring wall time may not exist and the autumn wall time can occur twice. Test the exact zones your product supports using current time-zone tables. Define whether users choose the earlier or later offset during an overlap; never silently guess for financial or operational deadlines.

Check time-zone table health

SELECT CONVERT_TZ(
  '2026-07-27 12:00:00',
  'UTC',
  'Africa/Lagos'
) AS converted;

A NULL result can indicate invalid arguments or missing named-zone data. Keep the server’s zone tables updated through the supported operating procedure and coordinate changes across replicas. Rule updates can alter conversion of future civil times.

Migrate without shifting existing wall-clock values

Changing a column from DATETIME to TIMESTAMP while sessions use a local zone can reinterpret every stored value as local and convert it. Stage the migration with an explicit expression and sample comparison:

ALTER TABLE legacy_events
ADD COLUMN occurred_at_new TIMESTAMP(6) NULL;

UPDATE legacy_events
SET occurred_at_new =
  CONVERT_TZ(occurred_at, 'Africa/Lagos', 'UTC')
WHERE occurred_at_new IS NULL
ORDER BY event_id
LIMIT 5000;

The correct source zone must come from the data contract, not the current server setting. Batch large tables, record rows that fall in ambiguous periods, verify counts and hashes, then cut readers over before dropping anything.

Compare old and new representations

SELECT event_id, occurred_at, occurred_at_new,
       TIMESTAMPDIFF(
         MICROSECOND,
         CONVERT_TZ(occurred_at, 'Africa/Lagos', 'UTC'),
         occurred_at_new
       ) AS difference_us
FROM legacy_events
WHERE event_id BETWEEN 100000 AND 101000
ORDER BY event_id;

Test boundary years, leap days, fractional seconds, DST transitions, replica sessions and application round trips. Retain the original column through the rollback window.

Keep transport formats unambiguous

{
  "occurred_at": "2026-07-27T12:00:00.123456Z",
  "appointment_local": "2026-10-25T09:00:00",
  "appointment_zone": "Africa/Lagos"
}

An API should not send 2026-07-27 12:00:00 and expect every client to infer the same zone. Use an offset or Z for instants. Send local time and zone identity separately when the wall-clock meaning is intentional.

Use a decision checklist

• Is this a global instant, local civil time, date or duration?
• Can the value fall outside 1970–2038?
• Should retrieval change with the session time zone?
• Must the original IANA zone and DST rule be preserved?
• What fractional precision survives the entire stack?
• Are connection time zones set and monitored explicitly?
• How will ambiguous and nonexistent local times be handled?

TIMESTAMP is a zone-converted instant with a narrow range. DATETIME is a broad wall-clock tuple with no stored zone. Choose from the business meaning, then enforce that meaning across connections and APIs.

Sources: the solved Stack Overflow question (question by Gad; accepted answer by blivet, CC BY-SA), and the MySQL 8.4 official documentation for DATE, DATETIME and TIMESTAMP, time-zone support, and automatic initialization.