Syntax\n------\n\nTIMESTAMP [(<microsecond precision)]\n\nDescription\n-----------\n\nA timestamp in the format YYYY-MM-DD HH:MM:SS.ffffff.\n\nThe timestamp field is generally used to define at which moment in time a row was added or updated and by default will automatically be assigned the current datetime when a record is inserted or updated. The automatic properties only apply to the first TIMESTAMP in the record; subsequent TIMESTAMP columns will not be changed.\n\nMariaDB includes the --mysql56-temporal-format option, on by default, which allows MariaDB to store TIMESTAMP values using the same low-level format MySQL 5.6 uses.\n\nFor more information, see Internal Format.\n\nSupported Values\n\nMariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (UTC). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' (UTC) and '2106-02-07 06:28:15 UTC'.\n\nMariaDB stores values that use the TIMESTAMP data type as the number of seconds since '1970-01-01 00:00:00' (UTC). This means that the TIMESTAMP data type can hold values between '1970-01-01 00:00:01' (UTC) and '2038-01-19 03:14:07' (UTC).\n\nMariaDB can also store microseconds with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.\n\nAutomatic Values\n\nMariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table when the system variable explicit_defaults_for_timestamp is not set (which was the default until MariaDB 10.10). For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:\n\n DEFAULT CURRENT_TIMESTAMP\n ON UPDATE CURRENT_TIMESTAMP\n\nThis means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.\n\nThis automatic initialization for INSERT and UPDATE queries can also be explicitly enabled for a column that uses the TIMESTAMP data type by specifying the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses for the column. In these clauses, any synonym of CURRENT_TIMESTAMP is accepted, including CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().\n\nThis automatic initialization for INSERT queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a constant DEFAULT value. For example, DEFAULT 0.\n\nThis automatic initialization for UPDATE queries can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying a DEFAULT clause for the column, but no ON UPDATE clause. If a DEFAULT clause is explicitly specified for a column that uses the TIMESTAMP data type, but an ON UPDATE clause is not specified for the column, then the timestamp value will not automatically change when an UPDATE statement is executed.\n\nMariaDB also has special behavior if NULL is assigned to column that uses the TIMESTAMP data type. If the column is assigned the NULL value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time. For details, see NULL values in MariaDB.\n\nThis automatic initialization for NULL values can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying the NULL attribute for the column. In this case, if the column's value is set to NULL, then the column's value will actually be set to NULL.\n\nTime Zones\n\nIf a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to Coordinated Universal Time (UTC) when stored, and converted back to the session's time zone when retrieved.\n\nMariaDB validates TIMESTAMP literals against the session's time zone. For example, if a specific time range never occurred in a specific time zone due to daylight savings time, then TIMESTAMP values within that range would be invalid for that time zone.\n\nMariaDB does not store any time zone identifier with the value of the TIMESTAMP data type. See MDEV-10018 for more information.\n\nMariaDB does not support time zone literals that contain time zone identifiers. See MDEV-11829 for more information.\n\nLimitations\n\n Because the TIMESTAMP value is stored as Epoch Seconds, the timestamp value '1970-01-01 00:00:00' (UTC) is reserved since the second #0 is used to represent '0000-00-00 00:00:00'.\n\nSQL_MODE=MAXDB\n\nIf the SQL_MODE is set to MAXDB, TIMESTAMP fields will be silently converted to DATETIME.\n\nInternal Format\n\nA temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the mysql56_temporal_format system variable.\n\nTables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the mysql56_temporal_format system variable was disabled continue to store data using the older data type format.\n\nIn order to update table columns from the older format to the newer format, execute an ALTER TABLE... MODIFY COLUMN statement that changes the column to the _same_ data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see MDEV-15225).\n\nFor instance, if you have a TIMESTAMP column in your table:\n\n``sql\nSHOW VARIABLES LIKE 'mysql56_temporal_format';\n\n+-------------------------+-------+\n| Variable_name           | Value |\n+-------------------------+-------+\n| mysql56_temporal_format | ON    |\n+-------------------------+-------+\n\nALTER TABLE example_table MODIFY ts_col TIMESTAMP;\n`\n\nWhen MariaDB executes the ALTER TABLE statement, it converts the data from the older temporal format to the newer one.\n\nIn the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using mariadb-dump. The columns using relevant temporal data types are restored using the new temporal format.\n\nColumns with old temporal formats are marked with a / mariadb-5.3 / comment in the output of SHOW CREATE TABLE, SHOW COLUMNS, DESCRIBE statements, as well as in the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS Table.\n\nColumns with old temporal formats are not marked with a / mariadb-5.3 / comment in the output of SHOW CREATE TABLE, SHOW COLUMNS, DESCRIBE statements, as well as in the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS Table.\n\n`sql\nSHOW CREATE TABLE mariadb5312_timestamp\\G\n*********************** 1. row ***********************\n       Table: mariadb5312_timestamp\nCreate Table: CREATE TABLE mariadb5312_timestamp (\n  ts0 timestamp / mariadb-5.3 / NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),\n  ts6 timestamp(6) / mariadb-5.3 / NOT NULL DEFAULT '0000-00-00 00:00:00.000000'\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\n``\n\nExamples\n--------\n\nCREATE TABLE t (id INT, ts TIMESTAMP);\n\nDESC t;\n+-------+-----------+------+-----+-------------------+-----------------------------+\n| Field | Type      | Null | Key | Default           | Extra                       |\n+-------+-----------+------+-----+-------------------+-----------------------------+\n| id    | int(11)   | YES  |     | NULL              |                             |\n| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n+-------+-----------+------+-----+-------------------+-----------------------------+\n\nINSERT INTO t(id)  VALUES (1),(2);\n\nSELECT  FROM t;\n+------+---------------------+\n| id   | ts                  |\n+------+---------------------+\n|    1 | 2013-07-22 12:50:05 |\n|    2 | 2013-07-22 12:50:05 |\n+------+---------------------+\n\nINSERT INTO t  VALUES (3,NULL),(4,'2001-07-22 12:12:12');\n\nSELECT * FROM t;\n+------+---------------------+\n| id   | ts                  |\n+------+---------------------+\n|    1 | 2013-07-22 12:50:05 |\n|    2 | 2013-07-22 12:50:05 |\n|    3 | 2013-07-22 12:51:56 |\n|    4 | 2001-07-22 12:12:12 |\n+------+---------------------+\n\nURL: https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp