Bulk Entry Time Change

Question

I've just switched my localization settings for the Daylight Savings folderol. (Something that, admittedly, I haven't done in previous years.) This year, I have over 200 entries in the queue that had their publish times set - and now, with the DST localization change, the publish time has all been changed. Is there a way to globally change entry times? I was looking at this question on bulk-entry status updating to see if I could figure out a way to tweak things, but I'm admittedly not super-strong on SQL syntax.

The changes I want to make are:
where an entry is in the future, and the time equals 8:15 AM,
change the time to 7:15 AM

Forgot to specify: I'm using EE 2.6x

Answer 1

Date math is a pain. Here's query that should give you entries in the future:

SELECT entry_id, title 
FROM exp_channel_titles 
WHERE UNIX_TIMESTAMP() < entry_date;

Double check this to make sure you are getting the 200 results you are expecting. Server Time zone, User time zone, DST settings, server time etc can all throw you off, but in any case it should only be a matter of hours.

Now we want to subtract 3600 seconds (the number of seconds in an hour) from those future records, like so:

UPDATE exp_channel_titles
SET entry_date = entry_date - 3600
WHERE UNIX_TIMESTAMP() < entry_date;

exp_channel_titles also has a column for 'day' so if subtracting one hour from an entry pulls it into the previous day you would really want to update that column too, not sure if there's a consequence of omitting this step and including it is pretty complicated.