October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want a string to be interpreted temporally.
Use the string in a temporal context to give MySQL a hint about how to treat it.
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 ...