mysql1411_mysql – #1411 – INSERT INTO … SELECT上函数str_to_date的日期时间值不正确

这些查询需要字符串到日期转换,因为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 ))

这将从我的表中选择ALL BUT最近添加的用户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到达与我查询的相反的第一行时,它将失败并显示上述消息.

我也试过COALESCE它们,这也适用于运行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会起作用？