| TIMESTAMP | DATETIME | |
|---|---|---|
| size | 4 Bytes / 8 Bytes | MySQL: 5 Bytes + fractional seconds storage MariaDB: 8 Bytes |
| description | MariaDB < 11.5: UNIX 32-bit time_t MariaDB >= 11.5: UNIX 64-bit time_t | Time in the currently selected time zone of the DB. Will display the same value, no matter what you change the time zone setting. However, the UNIX timestamp will differ when a time zone is used. |
| min | 1970-01-01 00:00:01 UTC | 1000-01-01 00:00:00 |
| max | MariaDB < 11.5: 2038-01-19 03:14:07 UTC MariaDB >= 11.5: 2106-02-07 06:28:15 UTC | 9999-12-31 23:59:59 |
| indexes | Yes | *NO* |
| cached | Yes | *NO* |
CREATE TABLE hmm(
unix timestamp,
dt datetime
);
SET time_zone = "+01:00";
INSERT INTO hmm VALUES(now(), now());
SELECT * FROM hmm;
-- 2022-12-23 16:15:21 2022-12-23 16:15:21
SET time_zone = "+00:00";
SELECT * FROM hmm;
-- 2022-12-23 15:15:21 2022-12-23 16:15:21
SET time_zone = "+00:00";
SELECT unix_timestamp(unix), unix_timestamp(dt) FROM hmm;
-- 1671808521 1671812121- MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
- By default, the current time zone for each connection is the server's time.
- The time zone can be set on a per-connection basis.
- As of MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table.
- For hour values less than than 10, a leading zero is required.
- The value '-00:00' is rejected.
- Time zone names such as 'EET' and 'Asia/Shanghai' cannot be used.
- The offset is not displayed when selecting a datetime value, even if one was used when inserting it.
- The range of supported offset values is -14:00 to +14:00, inclusive.
- Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled.
Last update: