How to convert MySQL datetime to Unix timestamp?
We can convert MySQL date and time to Unix Timestamp with the help of function UNIX_TIMESTAMP().
The following is the query.
mysql> SELECT UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p'));
After running the above query we will not get the output in date format as shown in the below output. The output shown here is a Unix Timestamp.
+------------------------------------------------------------------------+
| UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')) |
+------------------------------------------------------------------------+
| 1539966600 |
+------------------------------------------------------------------------+
1 row in set (0.04 sec)
To get the actual date/time, query is as follows.
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p');
The following is the output.
+-----------------------------------------------------------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p') |
+-----------------------------------------------------------------------------------------------------------+
| 10-19-2018 10:00:PM |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Published on 20-Nov-2018 17:29:30
