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)

Ankith Reddy

Updated on 30-Jul-2019 22:30:23

• Related Questions & Answers
• Convert MySQL timestamp to UNIX Timestamp?
• How to convert from Unix timestamp to MySQL timestamp value?
• How to convert timestamp to datetime in MySQL?
• Convert MySQL Unix-Timestamp format to date format?
• MySQL - Convert YYYY-MM-DD to UNIX timestamp
• How to convert Python date to Unix timestamp?
• Convert MM/DD/YY to Unix timestamp in MySQL?
• Convert dd/mm/yyyy string to Unix timestamp in MySQL?
• How to convert a Unix timestamp to time in JavaScript?
• How to convert TimeStamp to DateTime in Kotlin?\n
• Convert UNIX timestamp into human readable format in MySQL?
• How to convert unix timestamp string to readable date in Python?
• How to convert a datetime string to millisecond UNIX time stamp?
• How to convert timestamp string to datetime object in Python?
• How to get the Unix timestamp in C#