Recipe 6.20. 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:

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




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net