MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

11.2 Date and Time Data Types

The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each temporal type has a range of valid values, as well as a zero value that may be used when you specify an invalid value that MySQL cannot represent. The TIMESTAMP and DATETIME types have special automatic updating behavior, described in Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

For information about storage requirements of the temporal data types, see Section 11.7, “Data Type Storage Requirements”.

For descriptions of functions that operate on temporal values, see Section 12.7, “Date and Time Functions”.

Keep in mind these general considerations when working with date and time types:

The following table shows the format of the zero value for each type. The zero values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write. For temporal types that include a date part (DATE, DATETIME, and TIMESTAMP), use of these values may produce warning or errors. The precise behavior depends on which, if any, of the strict and NO_ZERO_DATE SQL modes are enabled; see Section 5.1.11, “Server SQL Modes”.

Data Type Zero Value
DATE '0000-00-00'
TIME '00:00:00'
DATETIME '0000-00-00 00:00:00'
TIMESTAMP '0000-00-00 00:00:00'
YEAR 0000