Quick summary
Summarize this blog with AI
MySQL DATETIME and TIMESTAMP: make the time contract explicit
This guide targets MySQL 8.4. DATETIME retains a calendar date and clock reading; TIMESTAMP participates in session-time-zone conversion when values are written and read. Neither stores the original IANA zone name. The choice also depends on supported date range, input parsing, and application driver behavior, not just whether a field is called created_at.
DATETIME does not choose a global instant for you
An offset-free DATETIME value such as 2026-01-15 12:00:00 is a clock reading. It can represent UTC under an explicit application contract, or a local appointment whose zone is stored separately. Reading it in another MySQL session time zone does not automatically shift the displayed clock. UTC DATETIME is a valid design when the application enforces conversion and needs dates beyond TIMESTAMP’s range.
Do not simplify this to “MySQL never converts DATETIME input.” MySQL 8.4 accepts date-time literals containing offsets, and input interpretation has additional rules. The example below deliberately uses offset-free inputs. Test the actual driver’s binding and serialization, including whether it sends an offset, rather than inferring behavior from how a timestamp looks in a log.
TIMESTAMP changes its display with the session time zone
For an offset-free TIMESTAMP input, MySQL interprets the clock in the session time zone, stores its UTC equivalent, and converts for retrieval. A different displayed time can therefore describe the same instant. TIMESTAMP in MySQL 8.4 has the familiar 1970–2038 range limit; DATETIME supports a much wider calendar range. Both allow fractional precision up to six digits when declared accordingly.
Reproduce the difference in an isolated MySQL session
This complete MySQL 8.4 example uses a temporary table and fixed UTC offsets, so it does not require installed named-time-zone tables. It changes only this connection’s time_zone and restores it at the end. Use a disposable session and a new table name; CREATE should fail if that temporary name is already in use. The example is documentation-checked; it was not executed on a native MySQL server during this revision.
SET @saved_demo_time_zone = @@session.time_zone;
SET SESSION time_zone = '+00:00';
CREATE TEMPORARY TABLE time_type_demo (
id integer PRIMARY KEY,
wall_clock DATETIME(6) NOT NULL,
event_instant TIMESTAMP(6) NOT NULL
);
INSERT INTO time_type_demo VALUES
(1,'2026-01-15 12:00:00.123456','2026-01-15 12:00:00.123456');
SELECT id,wall_clock,event_instant FROM time_type_demo ORDER BY id;
SET SESSION time_zone = '+02:00';
SELECT id,wall_clock,event_instant FROM time_type_demo ORDER BY id;
DROP TEMPORARY TABLE time_type_demo;
SET SESSION time_zone = @saved_demo_time_zone;At +00:00:
id | wall_clock | event_instant
1 | 2026-01-15 12:00:00.123456 | 2026-01-15 12:00:00.123456
At +02:00:
id | wall_clock | event_instant
1 | 2026-01-15 12:00:00.123456 | 2026-01-15 14:00:00.123456If an intermediate step fails, restore the saved session setting explicitly or close this disposable connection. Temporary tables disappear when the session closes. Do not substitute a global timezone change or a permanent production table to reproduce this behavior. SQL modes and warnings also matter for invalid dates; never disable strict validation merely to accept malformed input.
Choose by meaning, range, and recurrence
For an event instant within its supported range, TIMESTAMP with a known session time zone is one option. UTC DATETIME with enforced conversion is another; it requires disciplined application semantics. For a future local appointment, preserve the local date/time and named zone so the application can apply its daylight-saving rules. For a recurring opening hour, a full datetime may be the wrong model: weekday, local time, zone, and recurrence exceptions are separate facts.
A fixed offset such as +02:00 is not a named zone with seasonal changes. Ambiguous repeated local times and nonexistent spring-forward times require an explicit application policy. Do not “repair” them by guessing an offset from today’s settings. Neither column type remembers which interpretation a user intended.
Make every connection agree
Set and verify the session time zone when establishing or checking out a pooled connection. Document how the driver maps application datetime objects to SQL values and how the API represents an instant, for example with an explicit UTC offset. Avoid a workflow where the application shifts to UTC and another layer mistakenly shifts the same clock a second time.
For monthly reporting, convert the business interval’s start and next-month start to the storage convention once, then apply a half-open comparison. Do not assume every local day is 24 elapsed hours or end a timestamp month with the date of its last day. Keep conversion rules at a defined boundary and test both daylight-saving transitions in the relevant zones.
Debug the column, session, and driver separately
SELECT @@session.time_zone, @@global.time_zone, @@system_time_zone;
SELECT @@session.sql_mode;Inspect the real column definition, connection settings, original input value, driver binding, and retrieved value. Compare the same row in two disposable sessions with controlled offsets. A server global setting is only a default for relevant new connections; changing it does not retroactively standardize every existing pooled session. Named zones require maintained MySQL timezone data, as explained in its time zone support reference.
Document the contract alongside the schema
Write down whether each field is an instant, UTC clock, local appointment, or recurrence component, plus its precision and valid range. Use the MySQL date-time type documentation and literal syntax when testing inputs. A correct column type helps, but the complete application contract prevents the shift.
