How to get the total number of seconds from a MySQL DATETIME instance?

The MySQL DateTime instance can be converted into seconds with the help of UNIX_TIMESTAMP() function in the following way −

mysql> Select UNIX_TIMESTAMP('2017-05-15 04:05:30') AS 'NUMBER OF SECONDS';

+-------------------+
| NUMBER OF SECONDS |
+-------------------+
|        1494801330 |
+-------------------+
1 row in set (0.00 sec)

Above query will convert the given datetime instance into total number of seconds.

mysql> Select UNIX_TIMESTAMP(NOW()) AS 'NUMBER OF SECONDS';

+-------------------+
| NUMBER OF SECONDS |
+-------------------+
|        1509248856 |
+-------------------+
1 row in set (0.00 sec)

Above query will convert the current DateTime instance into a total number of seconds.

mysql> Select UNIX_TIMESTAMP(Dateofreg) AS 'NUMBER OF SECONDS' from
       testing where StudentName = 'Gaurav';

+-------------------+
| NUMBER OF SECONDS |
+-------------------+
|        1509247113 |
+-------------------+
1 row in set (0.00 sec)

Above query will convert the DateTime instance which is a stored value in a table ‘testing’ where column StudentName is having value ‘Gaurav’.

Jennifer Nicholas

Published on 19-Feb-2018 10:02:57

Previous Page Print Page