10

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

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.

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

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

hacket
  • 1,171
  • 4
  • 13
  • 25
  • Try a `CAST(STR_TO_DATE(...) AS DATETIME)` and see what happens. – MicSim Aug 30 '12 at 14:44
  • 1
    @hacket I think this is stupid, but in sql-server will give an error. Have you tried to do the insert without the () ? I mean, insert into fields select from.. If that works, I will put it as an answer – Gonzalo.- Aug 30 '12 at 15:43
  • 1
    @ElVieejo Just tried it, and I get the same error. Is this possibly a MySQL bug? – hacket Aug 30 '12 at 15:54
  • @hacket Did you get an error or a warning? – Devart Aug 31 '12 at 06:53
  • @Devart I'm not sure, I'll need to run it again. Is there a difference? – hacket Aug 31 '12 at 19:55
  • The error may abort script execution, the warning - is just an information and may say that something is wrong. Warnings can be checked with `SHOW WARNINGS` query. – Devart Sep 03 '12 at 15:20

1 Answers1

0

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

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

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 1
    I'm not really following your answer...do you mean because the format has the CST and CDT in it? – hacket Aug 31 '12 at 19:53
  • @hacket: Yes. And it has 'Mon' in it, too. Neither of those appear in the supported formats. – Mike Sherrill 'Cat Recall' Aug 31 '12 at 21:44
  • 1
    This is the only thing that makes sense to me. Whatever I do, it will not work. We are just going to use Excel to get rid of the duplicates, as crude as that is... – hacket Sep 04 '12 at 19:45