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

Converting TIME(N) and DATETIME(N) values ​​to numeric form can be done by adding 0 (0) to them. The following are the rules for this type of conversion −

Convert to integer

When N is 0, TIME(N) and DATETIME(N) values ​​will be converted to integers.

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

When N is greater than 0, TIME( N) and DATETIME(N) values ​​are converted to integers.

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)

The above is the detailed content of How do we convert TIME and DATETIME values ​​to numeric form in MySQL?. For more information, please follow other related articles on the PHP Chinese website!