How can we convert TIME and DATETIME values to numeric form in MySQL?

Conversion of TIME(N) and DATETIME(N) values to numeric form can be done by adding 0(+0) to them. Followings are the rules for such kind of conversion −

Converted to INTEGER

The TIME(N) and DATETIME(N) values will be converted to an integer when N is 0.

For example, the values of CURTIME() and NOW() can be converted to integer values as follows −

mysql> SELECT CURTIME(), CURTIME()+0; +-----------+-------------------+ | CURTIME() | CURTIME()+0       | +-----------+-------------------+ | 19:42:54  | 194254            | +-----------+-------------------+ 1 row in set (0.04 sec) mysql> SELECT NOW(), NOW()+0; +-------------------------+----------------------------------+ | NOW()                   | NOW()+0                          | +-------------------------+----------------------------------+ | 2017-10-27 19:43:43     | 20171027194343                   | +-------------------------+----------------------------------+ 1 row in set (0.00 sec)

Converted to DECIMAL

The TIME(N) and DATETIME(N) values will be converted to an integer when N is greater than 0.
For example, the values of CURTIME() and NOW() can be converted to decimal values as follows −

mysql> SELECT CURTIME(), CURTIME(3)+0; +-----------+-------------------+ | CURTIME() | CURTIME()+0       | +-----------+-------------------+ | 19:47:40  | 194740.575        | +-----------+-------------------+ 1 row in set (0.04 sec) mysql> SELECT NOW(), NOW(3)+0; +-------------------------+----------------------------------+ | NOW()                   | NOW()+0                          | +-------------------------+----------------------------------+ | 2017-10-27 19:48:45     | 20171027194845.589               | +-------------------------+----------------------------------+ 1 row in set (0.00 sec)