MySQL TIMESTAMP: Time Zones and Automatic Updates

MySQL TIMESTAMP stores a point in time. When a value is written, MySQL converts it from the session time zone to UTC; when it is read, MySQL converts it from UTC to the current session time zone. The time zone itself is not stored with each row.

Time zone conversion and range

The session time zone affects how MySQL interprets and displays TIMESTAMP values. If the same session inserts and reads a value without changing its time zone, the displayed value appears unchanged. A session using a different time zone sees the corresponding local time.

MySQL TIMESTAMP values range from 1970-01-01 00:00:01 UTC through 2038-01-19 03:14:07 UTC. For dates outside that range, or for a wall-clock date and time that should not be converted through a time zone, consider DATETIME.

You can check or set the current session time zone with:

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

For a daylight-saving schedule or a user’s local appointment time, store the applicable time-zone identifier separately when the application needs to preserve that context.

Set automatic creation and update times

Define automatic behavior explicitly. The created_at column gets the current timestamp when omitted from an INSERT; updated_at also changes when another column in the row changes:

CREATE TABLE event_log (
    event_id BIGINT PRIMARY KEY,
    message VARCHAR(100) NOT NULL,
    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)
);

Insert a row without supplying either timestamp:

INSERT INTO event_log (event_id, message)
VALUES (1, 'created');

When the message changes, MySQL updates updated_at automatically:

UPDATE event_log
SET message = 'edited'
WHERE event_id = 1;

If an UPDATE leaves every other column at its current value, the auto-update timestamp remains unchanged. To set it regardless, assign CURRENT_TIMESTAMP explicitly. When fractional-second precision is specified, use the same precision in the column and in CURRENT_TIMESTAMP, as in the (6) example above.

Legacy server configurations with explicit_defaults_for_timestamp disabled can give the first TIMESTAMP column implicit default and update behavior. Declare DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP explicitly instead of relying on that setting.

For full rules and exceptions, see MySQL’s automatic initialization and update documentation and TIMESTAMP type documentation.