Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 27.0Mb
PDF (RPM) - 25.4Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 160.0Kb
Man Pages (Zip) - 263.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Date and Time Type Overview

11.1.2 Date and Time Type Overview

A summary of the temporal data types follows. For additional information about properties and storage requirements of the temporal types, see Section 11.3, “Date and Time Types”, and Section 11.7, “Data Type Storage Requirements”. For descriptions of functions that operate on temporal values, see Section 12.7, “Date and Time Functions”.

For the DATE and DATETIME range descriptions, supported means that although earlier values might work, there is no guarantee.

  • DATE

    A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.


    A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format, but permits assignment of values to DATETIME columns using either strings or numbers.


    A timestamp. The range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the zero TIMESTAMP value.

    Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values. The automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, as described in Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

  • TIME

    A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'hh:mm:ss' format, but permits assignment of values to TIME columns using either strings or numbers.

  • YEAR[(2|4)]

    A year in 2-digit or 4-digit format. The default is 4-digit format. YEAR(2) or YEAR(4) differ in display format, but have the same range of values. In 4-digit format, values display as 1901 to 2155, or 0000. In 2-digit format, values display as 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY or YY format, but permits assignment of values to YEAR columns using either strings or numbers.


    The YEAR(2) data type has certain issues that you should consider before choosing to use it. As of MySQL 5.5.27, YEAR(2) is deprecated. For more information, see Section 11.3.4, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.

    For additional information about YEAR display format and interpretation of input values, see Section 11.3.3, “The YEAR Type”.

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

The MySQL server can be run with the MAXDB SQL mode enabled. In this case, TIMESTAMP is identical with DATETIME. If this mode is enabled at the time that a table is created, TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. See Section 5.1.10, “Server SQL Modes”.