MySQL Cookbook

Forcing MySQL to Treat Strings as Temporal Values

Problem

You want a string to be interpreted temporally.

Solution

Use the string in a temporal context to give MySQL a hint about how to treat it.

Discussion

If you need to make MySQL treat a string as a date or time, use it in an expression that provides a temporal context without changing the value. For example, you can’t add zero to a literal TIME string to cause a time-to-number conversion, but if you use TIME_TO_SEC( ) and SEC_TO_TIME( ), you can:

mysql> SELECT SEC_TO_TIME(TIME_TO_SEC('12:30:45'))+0;
+----------------------------------------+
| SEC_TO_TIME(TIME_TO_SEC('12:30:45'))+0 |
+----------------------------------------+
|                                 123045 |
+----------------------------------------+

The conversion to and from seconds leaves the value unchanged but results in a context where MySQL treats the result as a TIME value. For date values, the procedure is similar, but uses TO_DAYS( ) and FROM_DAYS( ):

mysql> SELECT '1999-01-01'+0, FROM_DAYS(TO_DAYS('1999-01-01'))+0;
+----------------+------------------------------------+
| '1999-01-01'+0 | FROM_DAYS(TO_DAYS('1999-01-01'))+0 |
+----------------+------------------------------------+
|           1999 |                           19990101 |
+----------------+------------------------------------+

For DATETIME- or TIMESTAMP-formatted strings, you can use DATE_ADD( ) to introduce a temporal context:

mysql> SELECT
    -> DATE_ADD('1999-01-01 12:30:45',INTERVAL 0 DAY)+0 AS 'numeric datetime',
    -> DATE_ADD('19990101123045',INTERVAL 0 DAY)+0 AS ...