#1411 -插入时函数str_to_date的日期时间值不正确...挑选

Question

这些查询需要将字符串转换为日期，因为时间戳存储为字符串，并且日志应用程序是不可更改的。

我有一个完美的选择查询 -

(SELECT main.user_id, main.Timestamp
FROM `user_table` main
WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) <
  (SELECT  MAX(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) 
   FROM `user_table` sub
   WHERE sub.user_id = main.user_id ))

这将从我的表中选择所有但最近添加的用户ID和时间戳。

但是，当我尝试将其插入到另一个表中时...就像这样——

INSERT INTO user_table_temp (`user_id`, `Timestamp`)
(SELECT main.user_id, main.Timestamp
FROM `user_table` main
WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) <
  (SELECT  MAX(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) 
   FROM `user_table` sub
   WHERE sub.user_id = main.user_id ))

我得到以下错误-

#1411 - Incorrect datetime value: 'Mon Mar 14 16:10:10 CDT 2011' for function str_to_date

这样做的原因是因为我的时间戳以两种格式存储。一种在非夏令时存储为“CST”，另一种在夏令时存储为“CDT”。当INSERT INTO SELECT到达与我查询的内容相反的第一行时，它将在上面的消息中失败。

我也尝试过合并它们，这在运行select时也是有效的

INSERT INTO user_table_temp  (`user_id `, `Timestamp`)
(SELECT main.user_id , main.Timestamp
FROM `user_table` main
WHERE COALESCE(STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ), STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) <
  (SELECT  MAX(COALESCE(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ), STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' ))) 
   FROM `user_table` sub
   WHERE sub.user_id = main.user_id ))

为什么插入时会失败，但选择会起作用？

Answer 1

我认为它在插入时会失败，因为你没有使用支持的格式。支持的格式。。。

作为“YYYY-MM-DD HH： MM： SS”或“YY-MM-DD HH： MM： SS”格式的字符串。这里也允许使用“宽松”语法：任何标点符号都可以用作日期部分或时间部分之间的分隔符。例如，“2012-12-31 11:30:45”、“2012^12^31 11 30 45”、“2012/12/31 11*30*45”和“2012@12@31 11^30^45”是等效的。

作为一个没有分隔符的字符串，格式为“yyyymmddhhmms”或“yyymddhhmms”，前提是该字符串作为日期有意义。例如，“20070523091528”和“070523091528”被解释为“2007-05-23 09:15:28”，但“071122129015”是非法的（它有一个无意义的分钟部分），并变为“0000-00-00 00:00:00”。

以yyyymmddhhmms或yymmddhhmms格式的数字，前提是该数字作为日期有意义。例如，19830905132800和830905132800被解释为“1983-09-05 13:28:00”。