mysql - #1411 - INSERT INTO...SELECT 函数 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 到达与我查询的内容相反的第一行时，它将失败并显示上述消息。

我也尝试过合并它们，这在运行选择时也有效 ->

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 ))

为什么这会在 INSERT 上失败，但 SELECT 会起作用？

Answer 1

我认为它在 INSERT 上失败，因为你没有使用 a supported format .支持的格式。 . .

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.