MySQL Cookbook, 2nd Edition

Treating Dates or Times as Numbers

Problem

You want to treat a temporal string as a number.

Solution

Perform a string-to-number conversion.

Discussion

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. For DATETIME columns, the conversion results in a fractional part. Use FLOOR() if that is not desired:

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, FLOOR(dt+0) FROM datetime_val; +---------------------+-----------------------+----------------+ | dt | dt+0 | FLOOR(dt+0) | +---------------------+-----------------------+----------------+ ...