将 MySQL 日期时间转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14014641/
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
Convert MySQL datetime to timestamp
提问by Maximus
I am trying to convert datetime into timestamp but mysql is giving me warnings and also converted values are wrong. Here is SQL query
我正在尝试将日期时间转换为时间戳，但 mysql 给了我警告，并且转换后的值也是错误的。这是 SQL 查询
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(STR_TO_DATE(A.date_added, '%M %d %Y %h:%i%p')) WHERE A.id=B.id;
Warnings
警告
+---------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2011-06-11 20:29:02' for function str_to_date |
+---------+------+--------------------------------------------------------------------------+
Result
结果
+---------------------+---------------------+
| date_added | date_added |
+---------------------+---------------------+
| 2012-02-23 06:12:45 | 2012-12-23 19:08:33 |
+---------------------+---------------------+
I also tried following query but it shows 0000-00-00 00:00:00 in timestamp field.
我也尝试了以下查询，但它在时间戳字段中显示 0000-00-00 00:00:00 。
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;
采纳答案by bonCodigo
Try this please:
请试试这个：
UPDATE table1 A, table2 B
SET B.date_added = FROM_UNIXTIME(A.date_added)
WHERE A.id=B.id
Reference. It seems like you have an issue with the way you format date stammp. Also please look into this post: Should I use field 'datetime' or 'timestamp'?
参考。您似乎对格式化日期戳的方式有问题。另请查看这篇文章：我应该使用字段“日期时间”还是“时间戳”？
回答by Omar S.
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;
UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;
UNIX_TIMESTAMP('2015-01-15 12:00:00');is sufficient to convert a mysql datetime to a Timestamp.
UNIX_TIMESTAMP('2015-01-15 12:00:00');足以将 mysql 日期时间转换为时间戳。
回答by Andrea
On MySQL 5.5 this works fine:
在 MySQL 5.5 上，这可以正常工作：
UPDATE `some_table`
SET `timestamp_col` = `datetime_col`
with datetime_colof type DATETIMEand timestamp_colof type TIMESTAMP.
用datetime_col的类型DATETIME和timestamp_col的类型TIMESTAMP。
That is: no needs of explicit type conversion.
即：不需要显式类型转换。
回答by Bill Karwin
You're using '%M %d %Y %h:%i%p'as the date format, but the warning shows a date string '2011-06-11 20:29:02', which doesn't match that format. I.e., the year is first, and the time is in 24-hour format.
您使用的'%M %d %Y %h:%i%p'是日期格式，但警告显示日期字符串“2011-06-11 20:29:02”，与该格式不匹配。即以年份为首，时间为24 小时制。
You could use '%Y-%M-%d %T'instead, that format would match the data.
您可以'%Y-%M-%d %T'改用，该格式将匹配数据。
See more on the date format codes here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
在此处查看有关日期格式代码的更多信息：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
