MySQL将datetime转换为Unix时间戳

How do I convert the following format to unix timestamp?

如何将以下格式转换为unix时间戳?

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:

我从DB获得的格式似乎在最后有AM。我试过用以下方法，但没有用:

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

where Sales.SalesDate value is Apr 15 2012 12:00AM

4 个解决方案

#1

174

Try this Query for CONVERT DATETIME to UNIX TIME STAMP

尝试此查询将DATETIME转换为UNIX时间戳

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

This Query for CHANGE DATE FORMATE

此查询用于更改日期格式

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')

#2

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.

当然，您必须使用STR_TO_DATE将您的日期转换为MySQL标准日期格式，并使用UNIX_TIMESTAMP从它获取时间戳。

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.

将给您一个有效的时间戳。查看STR_TO_DATE文档以获得关于格式字符串的更多信息。

#3

For current date just use UNIX_TIMESTAMP() in your mysql query.

对于当前日期，只需在mysql查询中使用UNIX_TIMESTAMP()。

#4

-5

From http://www.epochconverter.com/

从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

#1

174