These queries require converting a string to a date because the Timestamp is stored as a string and the registration application does not change.
I have a select query that works fine ->
(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 the last user ID and timestamp added from my table.
However, when I try to insert this into another table ... something like this →
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 that my timestamps are stored in two formats. One will be stored as "CST" during time saving, and the other will be stored as "CDT" during summer time. When an INSERT INTO SELECT hits the first line, which is the opposite of what I request, it will not work with the above message.
I also tried COALESCE them, which also works when running 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 will this happen with INSERT, but SELECT will work?