Why MySQL TIMESTAMP Is a Hidden Pitfall and How to Avoid It

MySQL has three common time types: DATE, DATETIME, TIMESTAMP. DATE stores only date (YYYY‑MM‑DD). DATETIME and TIMESTAMP store date and time (YYYY‑MM‑DD HH:MM:SS) and can include microseconds.

The TIMESTAMP type only supports values from 1970‑01‑01 00:00:01.000000 to 2038‑01‑19 03:14:07.999999, so it suffers from the “2038 problem” similar to Unix time_t.

Examples of real‑world bugs: an iPhone bug in 2016 that crashed when the system time was set to 1970‑01‑01 because of integer overflow in the underlying BSD‑based iOS time handling.

Automatic behavior of TIMESTAMP

When a TIMESTAMP column is defined, MySQL may silently add NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP to the first TIMESTAMP column, and a default value of '0000‑00‑00 00:00:00' to the second column, depending on server version and configuration.

These hidden defaults affect INSERT statements: inserting NULL into a TIMESTAMP column often stores the current time instead of NULL, and default values may be overridden.

mysql> CREATE TABLE t1 (
    ts1 timestamp,
    ts2 timestamp,
    ts3 timestamp default '2010-01-01 00:00:00'
) ENGINE=InnoDB;
...

If the server variable explicit_defaults_for_timestamp is OFF (the default), MySQL applies the automatic defaults described above.

When time_zone=SYSTEM is set, each timestamp conversion requires a global mutex, causing high CPU usage and latency under heavy concurrency.

In non‑strict mode, inserting a value outside the TIMESTAMP range does not raise an error; MySQL stores '0000‑00‑00 00:00:00' and issues a warning.

Changing explicit_defaults_for_timestamp to ON

Setting explicit_defaults_for_timestamp=ON disables the automatic defaults. Table creation becomes straightforward, and NULL can be stored in TIMESTAMP columns when allowed.

mysql> SET @@global.explicit_defaults_for_timestamp=ON;
mysql> CREATE TABLE t2 (
    ts1 timestamp NULL,
    ts2 timestamp NULL,
    ts3 timestamp default '2010-01-01 00:00:00'
) ENGINE=InnoDB;
...

With STRICT_TRANS_TABLES enabled, inserting NULL into a NOT NULL TIMESTAMP column raises an error; otherwise MySQL inserts '0000‑00‑00 00:00:00' and generates a warning.

High‑concurrency considerations

If time_zone=SYSTEM , every timestamp conversion calls the system library to obtain the current time zone, which may be protected by a global mutex and become a bottleneck under high load.

Range validation

In non‑strict mode, values beyond the TIMESTAMP range are accepted with a warning, and the stored value may be clamped to the nearest representable timestamp.

mysql> INSERT INTO t1 VALUES ('2039-01-01 00:00:00', NULL, NULL);
Query OK, 1 row affected, 1 warning
...

Conclusion

Because of its quirky defaults and the 2038 limitation, TIMESTAMP should be used sparingly. When maintaining legacy systems that rely on TIMESTAMP, test thoroughly and consider switching to DATETIME or adjusting server variables.

References

MySQL Time‑Zone Support

CSDN article

The Year 2038 Problem

SegmentFault discussion

iThome news