162

How do I convert the following format to unix timestamp?

Apr 15 2012 12:00AM

The format I get from DB seems to have AM at the end. I've tried using the following but it did not work:

CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE,  
CONVERT(TIMESTAMP, Sales.SalesDate, 103) AS TSSALESDATE

where Sales.SalesDate value is Apr 15 2012 12:00AM
jeremysawesome
  • 7,033
  • 5
  • 33
  • 37
redcoder
  • 2,233
  • 4
  • 22
  • 24

4 Answers4

261

Here's an example of how to convert DATETIME to UNIX timestamp:
SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))

Here's an example of how to change date format:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p')

Documentation: UNIX_TIMESTAMP, FROM_UNIXTIME

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Query Master
  • 6,989
  • 5
  • 35
  • 58
  • 1
    Thanks .This works.This can also be used in other statements such as update,delete,insert etc – MR_AMDEV Jul 01 '19 at 08:35
  • 1
    After so many trials your solution worked for me: ```select * from (SELECT order_increment_id, FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(order_date, '%d %M %Y %h:%i:%s %p')),'%Y-%m-%d') as order_date, email_sent FROM `packingslip_header` where email_sent=0) t where order_date >= '2019-11-13' ORDER BY order_increment_id ASC``` This is the date I had ```31 Oct 2017 4:16:49 pm``` so I needed to use ```%d %M %Y %h:%i:%s %p``` for ```STR_TO_DATE``` – Damodar Bashyal Nov 15 '19 at 01:49
38

You will certainly have to use both STR_TO_DATE to convert your date to a MySQL standard date format, and UNIX_TIMESTAMP to get the timestamp from it.

Given the format of your date, something like 

UNIX_TIMESTAMP(STR_TO_DATE(Sales.SalesDate, '%M %e %Y %h:%i%p'))

Will gives you a valid timestamp. Look the STR_TO_DATE documentation to have more information on the format string.

strnk
  • 2,013
  • 18
  • 21
0

If you want to create a timestamp as returned by java's Date.getTime() you should multiply by 1000.

SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))*1000

Now for a more standard date format use:

SELECT UNIX_TIMESTAMP(STR_TO_DATE('2022-12-14 20:58:00', '%Y-%m-%d %H:%i:%s'))*1000
Marinos An
  • 9,481
  • 6
  • 63
  • 96
-5

From http://www.epochconverter.com/

SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())

My bad, SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD. More on using timestamps with MySQL:

http://www.epochconverter.com/programming/mysql-from-unixtime.php
Saty
  • 22,443
  • 7
  • 33
  • 51
Brian Smith
  • 11
  • 1