＃1411 - INSERT INTO ... SELECT中函数str_to_date的datetime值不正确 [英] #1411 - Incorrect datetime value for function str_to_date on INSERT INTO...SELECT

问题描述

这些查询需要字符串进行日期转换，因为Timestamp存储为字符串，日志应用程序是不可更改的。

These queries require the string to date conversion because Timestamp is stored as a string, and the logging application is unchangeable.

我有一个选择查询， >

I have a select query that works perfectly ->

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

这将从我的表中选择全部但最近添加的用户标识和时间戳。

This will select ALL BUT most recently added user id and timestamp from my table.

但是，当我尝试将这个插入另一个表...像这样 - >

However, when I try to insert this into another table...like so ->

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

我收到以下错误 - >

I get the following error ->

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

原因是因为我的Timestamps以两种格式存储。在非夏令时间内，将以CST存储，另一个将在夏令时期间存储为CDT。当INSERT INTO SELECT进入与我正在查询的对象相反的第一行时，它将失败并显示上述消息。

The reason for this is because my Timestamps are stored in two formats. One will store as 'CST' during non-daylight-savings time, the other will store as 'CDT' during daylight-savings time. When the INSERT INTO SELECT gets to the first row that has the opposite of what I'm querying against, it will fail with the above message.

我还试图COALESCE他们，这也可以在运行select - >

I have also tried to COALESCE them, which also works when just running the 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会起作用？

Why would this fail on an INSERT, but the SELECT would work?

推荐答案

我认为INSERT失败，因为你不使用支持的格式。支持的格式。 。

I think it fails on INSERT because you're not using a supported format. The supported formats . . .

作为'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 @作为YYYYMMDDHHMMSS或
'YYMMDDHHMMSS'格式中没有分隔符的字符串，只要它们是等价的。

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.

字符串作为日期有意义。
例如，'20070523091528'和'070523091528'被解释为
'2007-05-23 09:15:28'，但'071122129015'是非法的（它有一个
无意义的分钟部分），并变为0000-00-00 00:00:00。

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

作为YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，提供
的数字有意义的日期。例如，19830905132800和
830905132800被解释为'1983-09-05 13:28: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'.

这篇关于＃1411 - INSERT INTO ... SELECT中函数str_to_date的datetime值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！