MySQL 字符串到mysql中的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8587177/
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
String to timestamp in mysql
提问by AvMishra
Is there any way to convert string to UNIX timestamp in MySQL?
有没有办法在 MySQL 中将字符串转换为 UNIX 时间戳？
For example I have string 2011-12-21 02:20pmwhich needs to be in unix timestamp format.
例如，我有2011-12-21 02:20pm需要采用 unix 时间戳格式的字符串。
回答by a'r
UNIX_TIMESTAMP()does the trick:
UNIX_TIMESTAMP()诀窍：
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_DATEfirst like this:
但是，该UNIX_TIMESTAMP()函数仅采用标准的 MySQL 格式日期。如果你想使用 AM/PM 符号，你需要STR_TO_DATE先像这样使用：
SELECT UNIX_TIMESTAMP(
STR_TO_DATE('2011-12-21 02:20pm', '%Y-%m-%d %h:%i%p')
);
回答by sactiw
Though @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 ':'.
虽然@a'r 已经给出了正确的答案，但我仍然想在这里补充的是，两个参数STR_TO_DATE()函数，“日期字符串”格式和“日期格式”字符串，应该有匹配的“-”位置和 '：'。
For example following 4 queries return exact same result 2014-05-28 11:30:10
例如以下 4 个查询返回完全相同的结果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 ':'
注意：每个查询中STR_TO_DATE()函数的 2 个参数具有匹配的“-”和“:”位置
