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)

Ramu Prasad

Updated on: 2020-06-19T13:43:29+05:30

652 Views

Kickstart Your Career

Get certified by completing the course

Get Started