Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.5Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Conversion Between Date and Time Types

11.3.7 Conversion Between Date and Time Types

To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of valid values for the resulting type. For example, although DATE, DATETIME, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. TIMESTAMP values cannot be earlier than 1970 UTC or later than '2038-01-19 03:14:07' UTC. This means that a date such as '1968-01-01', while valid as a DATE or DATETIME value, is not valid as a TIMESTAMP value and is converted to 0.

Conversion of DATE values:

  • Conversion to a DATETIME or TIMESTAMP value adds a time part of '00:00:00' because the DATE value contains no time information.

  • Conversion to a TIME value is not useful; the result is '00:00:00'.

Conversion of DATETIME and TIMESTAMP values:

  • Before MySQL 5.6.4, conversion to a DATE value discards the time part because the DATE type contains no time information. As of MySQL 5.6.4, fractional seconds are taken into account and conversion to DATE rounds the time part. For example, '1999-12-31 23:59:59.499' becomes '1999-12-31', whereas '1999-12-31 23:59:59.500' becomes '2000-01-01'.

  • Conversion to a TIME value discards the date part because the TIME type contains no date information.

Conversion of TIME values to other temporal types is version specific:

  • As of MySQL 5.6.4, the value of CURRENT_DATE() is used for the date part. The TIME is interpreted as elapsed time (not time of day) and added to the date. This means that the date part of the result differs from the current date if the time value is outside the range from '00:00:00' to '23:59:59'.

    Suppose that the current date is '2012-01-01'. TIME values of '12:00:00', '24:00:00', and '-12:00:00', when converted to DATETIME or TIMESTAMP values, result in '2012-01-01 12:00:00', '2012-01-02 00:00:00', and '2011-12-31 12:00:00', respectively.

    Conversion of TIME to DATE is similar but discards the time part from the result: '2012-01-01', '2012-01-02', and '2011-12-31', respectively.

  • Before 5.6.4, MySQL converts a time value to a date or date-and-time value by parsing the string value of the time as a date or date-and-time. This is unlikely to be useful. For example, '23:12:31' interpreted as a date becomes '2023-12-31'. Time values not valid as dates become '0000-00-00' or NULL.

Explicit conversion can be used to override implicit conversion. For example, in comparison of DATE and DATETIME values, the DATE value is coerced to the DATETIME type by adding a time part of '00:00:00'. To perform the comparison by ignoring the time part of the DATETIME value instead, use the CAST() function in the following way:

date_col = CAST(datetime_col AS DATE)

Conversion of TIME and DATETIME values to numeric form (for example, by adding +0) occurs as follows:

  • As of MySQL 5.6.4, TIME(N) or DATETIME(N) is converted to integer when N is 0 (or omitted) and to a DECIMAL value with N decimal digits when N is greater than 0:

    mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
    +-----------+-------------+--------------+
    | CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
    +-----------+-------------+--------------+
    | 09:28:00  |       92800 |    92800.887 |
    +-----------+-------------+--------------+
    mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
    +---------------------+----------------+--------------------+
    | NOW()               | NOW()+0        | NOW(3)+0           |
    +---------------------+----------------+--------------------+
    | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
    +---------------------+----------------+--------------------+
    
  • Before MySQL 5.6.4, the conversion results in a double-precision value with a microseconds part of .000000:

    mysql> SELECT CURTIME(), CURTIME()+0;
    +-----------+--------------+
    | CURTIME() | CURTIME()+0  |
    +-----------+--------------+
    | 09:28:00  | 92800.000000 |
    +-----------+--------------+
    mysql> SELECT NOW(), NOW()+0;
    +---------------------+-----------------------+
    | NOW()               | NOW()+0               |
    +---------------------+-----------------------+
    | 2012-08-15 09:28:00 | 20120815092800.000000 |
    +---------------------+-----------------------+
    

User Comments
Sign Up Login You must be logged in to post a comment.