Date and Time Types

MySQL provides types to represent temporal data in various forms. Date and time types are available, either together or in combination. There is a special timestamp type that is updated automatically when a record changes and a type for storing years if you don't need a complete date.

The terms CC, YY, MM, and DD in date formats represent century, year, month, and day. The terms hh, mm, and ss in time formats represent hour, minute, and second.

DATE

Meaning: A date, in 'CCYY-MM-DD' format

Allowable attributes: None, other than the global attributes

Range: '1000-01-01' to '9999-12-31'

Zero value: '0000-00-00'

Default value: NULL if column can be NULL, '0000-00-00' if NOT NULL

Storage required: 3 bytes (4 bytes prior to MySQL 3.22)

TIME

Meaning: A time, in 'hh:mm:ss' format ('-hh:mm:ss' for negative values); represents elapsed time but can be treated as time of day.

Allowable attributes: None, other than the global attributes.

Range: ' 838:59:59' to '838:59:59'.

Zero value: '00:00:00'.

Default value: NULL if column can be NULL, '00:00:00' if NOT NULL.

Storage required: 3 bytes.

Note: Although '00:00:00' is used as the zero value when illegal values are inserted into a TIME column, that is also a legal value that lies within the normal column range.

DATETIME

Meaning: A date and time value, in 'CCYY-MM-DD hh:mm:ss' format

Allowable attributes: None, other than the global attributes

Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

Zero value: '0000-00-00 00:00:00'

Default value: NULL if column can be NULL, '0000-00-00 00:00:00' if NOT NULL

Storage required: 8 bytes

TIMESTAMP[(M)]

Meaning: A timestamp (date and time), in CCYYMMDDhhmmss format. Inserting a NULL into any TIMESTAMP column of a table inserts the current date and time. In addition, changing the value of any other column in the row causes the first TIMESTAMP column to be updated to the date and time at which the modification occurs. Values are stored and used in calculations internally to full 14-character precision, regardless of the display width.

Allowable attributes: None, other than the global attributes.

Range: 19700101000000 to sometime in the year 2037.

Zero value: 00000000000000.

Default value: The current date and time for the first TIMESTAMP column in a table, 0 for any others. Note that DESCRIBE and SHOW COLUMNS report the default value for TIMESTAMP columns as NULL, though you cannot insert a literal NULL into such a column. This indicates that you set a TIMESTAMP to the current date and time when you create a new record by setting it to NULL.

Storage required: 4 bytes.

Note: If NOT NULL is specified as an attribute, it is ignored.

YEAR[(M)]

Meaning: A year value. If given, M must be 2 or 4 for formats of YY or CCYY. If omitted, M defaults to 4.

Allowable attributes: None, other than the global attributes.

Range: 1901 to 2155, and 0000 for YEAR(4). 1970 to 2069 for YEAR(2), but only the last two digits are displayed.

Zero value: 0000 for YEAR(4), 00 for YEAR(2).

Default value: NULL if column can be NULL, 0000 or 00 if NOT NULL.

Storage required: 1 byte.