Is there any way to convert string to UNIX timestamp in MySQL?
For example, I have the string 2011-12-21 02:20pm which needs to be in Unix timestamp format.
-
1dev.mysql.com/doc/refman/5.5/en/…cristian– cristian2011-12-21 08:56:35 +00:00Commented Dec 21, 2011 at 8:56
Add a comment
|
2 Answers
UNIX_TIMESTAMP() does the trick:
SELECT UNIX_TIMESTAMP('2011-12-21 14:20:00');
However, the UNIX_TIMESTAMP() function only takes a standard MySQL formatted date. If you want to use the AM/PM notation, you will need to use STR_TO_DATE first like this:
SELECT UNIX_TIMESTAMP(
STR_TO_DATE('2011-12-21 02:20pm', '%Y-%m-%d %h:%i%p')
);
1 Comment
akenney
And fyi you can use
from_unixtime() to convert a unix timestamp back into a timestamp objectThough @a'r has already given the correct answer, still something I would like to add here is that the two params STR_TO_DATE() function, 'date string' format and 'date format' string, should have matching placement of '-' and ':'.
For example following 4 queries return exact same result 2014-05-28 11:30:10
SELECT STR_TO_DATE('2014-05-28 11:30:10','%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('20140528 11:30:10','%Y%m%d %H:%i:%s');
SELECT STR_TO_DATE('2014-05-28 113010','%Y-%m-%d %H%i%s') ;
SELECT STR_TO_DATE('20140528 113010','%Y%m%d %H%i%s');
Note: the 2 params to STR_TO_DATE() function in each query has matching placement for '-' and ':'
