10.1.2. Overview of Date and Time Types

A summary of the temporal data types follows. For additional information about properties of the temporal types, see Section 10.3, “Date and Time Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 11.7, “Date and Time Functions”.

For the DATETIME and DATE 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.

  • DATETIME

    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.

  • TIMESTAMP

    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 “zeroTIMESTAMP value.

    A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 10.3.1.1, “TIMESTAMP Properties”.

    A TIMESTAMP value is returned as a string in the format 'YYYY-MM-DD HH:MM:SS' with a display width fixed at 19 characters. To obtain the value as a number, you should add +0 to the timestamp column.

    Note

    The TIMESTAMP format that was used prior to MySQL 4.1 is not supported in MySQL 5.5; see MySQL 3.23, 4.0, 4.1 Reference Manual for information regarding the old format.

  • 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 two-digit or four-digit format. The default is four-digit format. In four-digit format, the permissible values are 1901 to 2155, and 0000. In two-digit format, the permissible values are 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers.

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

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
Copyright © 2010-2017 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout