October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want to treat a temporal string as a number.
Perform a string-to-number conversion.
In many cases, it is possible in MySQL to treat date and time values as numbers. This can sometimes be useful if you want to perform an arithmetic operation on the value. To force conversion of a temporal value to numeric form, add zero or use it in a numeric context:
mysql>SELECT t1,->t1+0 AS 't1 as number',->FLOOR(t1) AS 't1 as number',->FLOOR(t1/10000) AS 'hour part'->FROM time_val;+----------+--------------+--------------+-----------+ | t1 | t1 as number | t1 as number | hour part | +----------+--------------+--------------+-----------+ | 15:00:00 | 150000 | 150000 | 15 | | 05:01:30 | 50130 | 50130 | 5 | | 12:30:20 | 123020 | 123020 | 12 | +----------+--------------+--------------+-----------+
The same kind of conversion can be performed for date or date-and-time values:
mysql>SELECT d, d+0 FROM date_val;+------------+----------+ | d | d+0 | +------------+----------+ | 1864-02-28 | 18640228 | | 1900-01-15 | 19000115 | | 1987-03-05 | 19870305 | | 1999-12-31 | 19991231 | | 2000-06-04 | 20000604 | +------------+----------+ mysql>SELECT dt, dt+0 FROM datetime_val;+---------------------+----------------+ | dt | dt+0 | +---------------------+----------------+ | 1970-01-01 00:00:00 | 19700101000000 | | 1987-03-05 12:30:15 | 19870305123015 | | 1999-12-31 09:00:00 | 19991231090000 | | 2000-06-04 15:45:30 | 20000604154530 | +---------------------+----------------+ ...