Date and Time Types

MySQL provides several types to represent temporal data. Types are available for dates and times, either separate or in combination. There is a special timestamp type that is updated automatically when a record changes, and a type for storing years when you don't need a complete date.

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

• 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 the column can be NULL, '0000-00-00' if NOT NULL

  Storage required. 3 bytes

• 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 the column can be NULL, '0000-00-00 00:00:00' if NOT NULL

  Storage required. 8 bytes

• TIME

  Meaning. A time, in 'hh:mm:ss' format (or '-hh:mm:ss' for negative values); represents elapsed time but may 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 the 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, it is also a legal value that lies within the normal column range.

• TIMESTAMP

  Meaning. A timestamp (date and time), in 'CCYY-MM-DD hh:mm:ss' format. The TIMESTAMP type has several special behaviors. Normally, inserting a NULL into any TIMESTAMP column of a table inserts the current date and time. One TIMESTAMP column per table can have a default value of the current timestamp when a record is created. Also, changing the value of any other column in a record later causes this TIMESTAMP column to be updated to the date and time at which the modification occurs. Prior to MySQL 4.1.2, this column is always the first TIMESTAMP in the table. As of MySQL 4.1.2, you can designate which TIMESTAMP should be treated this way, and you can suppress automatic initialization, updating, or both.

  Allowable attributes. As of MySQL 4.1.2, any one TIMESTAMP column in a table can have attributes of DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_ TIMESTAMP, or both. (You cannot use one attribute with one TIMESTAMP column and the other attribute with another TIMESTAMP column, nor can you use either attribute with more than one TIMESTAMP column). DEFAULT CURRENT_TIMESTAMP causes the column to be set to the current date and time at record creation time if no value is given for the column. ON UPDATE CURRENT_TIMESTAMP causes the column to be updated the current date and time when any other column in the record is changed from its current value. CURRENT_TIMESTAMP() and NOW() are understood as synonyms for CURRENT_TIMESTAMP.

  A constant DEFAULT value can be specified to assign a TIMESTAMP column a fixed date and time value or zero.

  Prior to MySQL 4.1.6, setting a TIMESTAMP column to NULL always sets it to the current date and time. As of MySQL 4.1.6, the NULL attribute can be given to allow a TIMESTAMP column to store NULL values. Without this attribute, storing a NULL into a TIMESTAMP column sets it to the current date and time.

  Range. '1970-01-01 00:00:00' to partially through the year 2037

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

  Default value. As of MySQL 4.1.2, DESCRIBE and SHOW COLUMNS display the default value as CURRENT_TIMESTAMP if the column is set automatically to the current date and time when records are created. Otherwise the constant date and time default value is displayed. See the discussion of the allowable attributes.

  Prior to MySQL 4.1.2, the default for the first TIMESTAMP column is displayed as NULL. This indicates that the column's default is the current date and time, because assigning a value of NULL when you create a new record sets the column to the current date and time. For other TIMESTAMP columns, the default is 0.

  Storage required. 4 bytes

  During table creation, TIMESTAMP columns are subject to the setting of the SQL mode. If the MAXDB SQL mode is enabled, any TIMESTAMP column is created as a DATETIME column instead, for compatibility with the MaxDB DBMS.

• 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 the column can be NULL, 0000 or 00 if NOT NULL

  Storage required. 1 byte