将MySQL日期时间转换为时间戳 [英] Convert MySQL datetime to timestamp

问题描述

我试图将日期时间转换为时间戳，但是mysql给我警告，并且转换后的值是错误的.这是SQL查询

I am trying to convert datetime into timestamp but mysql is giving me warnings and also converted values are wrong. Here is SQL query

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;

警告

+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2011-06-11 20:29:02' for function str_to_date |
+---------+------+--------------------------------------------------------------------------+

结果

+---------------------+---------------------+
| date_added          | date_added          |
+---------------------+---------------------+
| 2012-02-23 06:12:45 | 2012-12-23 19:08:33 |
+---------------------+---------------------+

我也尝试了以下查询，但是它在时间戳字段中显示了注册时间00:00:00.

I also tried following query but it shows 0000-00-00 00:00:00 in timestamp field.

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 = FROM_UNIXTIME(A.date_added) 
WHERE A.id=B.id

参考. 似乎日期格式的格式化方式存在问题. 另外，请查看这篇文章:我应该使用字段"datetime"还是"timestamp"?

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

这篇关于将MySQL日期时间转换为时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！