MySQL Cookbook, 2nd Edition

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-formatted string to effect a time-to-number conversion, but if you use TIME_TO_SEC() and SEC_TO_TIME(), you can. The two columns of the following result demonstrate this:

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

In the second column, 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 date-and-time values, you can use DATE_ADD() to introduce a temporal context: ...