Forcing MySQL to Treat Strings as Temporal Values

5.30.1 Problem

You want a string to be interpreted temporally.

5.30.2 Solution

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

5.30.3 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 'numeric timestamp'; +------------------+-------------------+ | numeric datetime | numeric timestamp | +------------------+-------------------+ | 19990101123045 | 19990101123045 | +------------------+-------------------+