MySQL 将日期字符串转换为 Unix 时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11133760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL convert date string to Unix timestamp
提问by redcoder
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 AMat 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
回答by Query Master
Try this Query for CONVERT DATETIME to UNIX TIME STAMP
试试这个查询 CONVERT DATETIME to UNIX TIME STAMP
SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))
This Query for CHANGE DATE FORMATE
这个查询 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')
回答by strnk
You will certainly have to use both STR_TO_DATEto convert your date to a MySQL standard date format, and UNIX_TIMESTAMPto 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_DATEdocumentation to have more information on the format string.
Will 给你一个有效的时间戳。查看STR_TO_DATE文档以获取有关格式字符串的更多信息。
回答by stackMonk
For current date just use UNIX_TIMESTAMP()in your MySQL query.
对于当前日期，只需UNIX_TIMESTAMP()在您的 MySQL 查询中使用。
回答by Brian Smith
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
