Adding time to rows with a specific timestamp in a database

Question

I have a database table that includes a timestamp for each record. Everyday this database is updated by a cron that was set to run 1 minute before gmt midnight (23:59:00). We are changing the cron to run at exactly midnight now (00:00:00), so I need to update all fields that were logged at 23:59 to 00:00 of the next day (2013-05-21 23:59:00 should update to 2013-05-22 00:00:00).

The update script was set to capture the timestamp at script start, but because it was poorly written it didn't account for seconds so some records have a start time of 2013-05-21 23:59:01, some may have 2013-05-20 23:59:02 or even 2013-05-19 23:59:03. All of these will need to be updated to 00:00:00 of the next day.

There are thousands of other records that were not updated by the cron and therefore have random timestamps. These records need to be left unaffected. For example 2013-05-19 23:13:47, 2013-05-19 02:50:56, and 2013-05-19 16:42:13 should all be left untouched.

I think the following code from this post is somewhat along the lines of what I'm looking for, but after some googling and testing myself I haven't had much luck.

UPDATE table
 SET `time` = CASE
              WHEN CURRENT_TIMESTAMP>='23:59:00' 
              THEN CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
              ELSE CURRENT_TIMESTAMP END

Any time with 23:59:xx can be safely updated to midnight, although I think the latest cron I've found to run took place at 23:59:04 if it matters. — Capt Otis, May 22 '13 at 06:09

score 1 · Accepted Answer · answered May 22 '13 at 06:17

1

Try this query -

UPDATE
  table
SET
  `time` = DATE(`time`) + INTERVAL 1 DAY
WHERE
  TIME(`time`) >= '23:59:00'

answered May 22 '13 at 06:17

Devart

119,203
23
166
186

Adding time to rows with a specific timestamp in a database

1 Answers1