We're updating the issue view to help you get more done.Learn more

Make "CAST(time_expr AS DATETIME)" compatible with MySQL-5.6 (and the SQL Standard)

CAST(time_expr AS DATETIME) and CAST(time_expr AS DATE)
work differently in MariaDB-10.0 and MySQL-5.6.

MySQL-5.6:

mysql> SELECT CURRENT_DATE, CAST(TIME'48:10:10' AS DATETIME), CAST(TIME'48:10:10' AS DATE);
+--------------+----------------------------------+------------------------------+
| CURRENT_DATE | CAST(TIME'48:10:10' AS DATETIME) | CAST(TIME'48:10:10' AS DATE) |
+--------------+----------------------------------+------------------------------+
| 2013-12-02   | 2013-12-04 00:10:10              | 2013-12-04                   |
+--------------+----------------------------------+------------------------------+
1 row in set (0.00 sec)

MariaDB-10.0:

mysql> SELECT CURRENT_DATE, CAST(TIME'48:10:10' AS DATETIME), CAST(TIME'48:10:10' AS DATE);
+--------------+----------------------------------+------------------------------+
| CURRENT_DATE | CAST(TIME'48:10:10' AS DATETIME) | CAST(TIME'48:10:10' AS DATE) |
+--------------+----------------------------------+------------------------------+
| 2013-12-02   | 0000-00-02 00:10:10              | 0000-00-00                   |
+--------------+----------------------------------+------------------------------+
1 row in set (0.01 sec)

Starting from the version 5.6, MySQL switched to the SQL Standard
behaviour when casting TIME to DATETIME, i.e. by adding CURRENT_DATE
to the time value.

MySQL-5.6 also uses CURRENT_DATE when casting from TIME to DATE for consistency
(this is a non-standard extension, the standard disallows CAST from TIME to DATE).

Pre-5.6 versions of MySQL did not use CURRENT_DATE when doing such casts.
MariaDB-10.0 still demonstrates the pre-5.6 behaviour.

Note, Oracle 11g also uses the standard behaviour:

SQL> SELECT CURRENT_DATE, CAST(TIME'10:10:10' AS TIMESTAMP) FROM DUAL;

CURRENT_DATE
------------------
CAST(TIME'10:10:10'ASTIMESTAMP)
---------------------------------------------------------------------------
02-DEC-13
02-DEC-13 10.10.10.000000 AM

An excerpt from the SQL standard, Section 6.12 <cast specification>:

<cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>
<cast operand> ::= <value expression> | <null specification> | <empty specification>

.. let TD be the data type identified by <data type>
.. let SD be the declared type of the <value expression>
.. SV is the source value
.. TV is the target value

17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE...
c) If SD is TIME WITHOUT TIME ZONE, then the <primary datetime field>s
year, month, and day of TV are set to their respective values in an execution
of CURRENT_DATE and the <primary datetime field>s hour, minute, and second
of TV are set to their respective values in SV, with implementation-
defined rounding or truncation if necessary.

Status

Assignee

Alexander Barkov

Reporter

Alexander Barkov