Search code examples
mysqldatetimemysql-event

MySQL deleting row Earlier than its should

I have an event that runs every hour on my database. It calls a procedure that deletes rows from my photo table older than a day. Here is the query:

DELETE FROM Photos WHERE created_at < (NOW() - INTERVAL 1 DAY);

However, this morning I noticed that the row I was testing was deleted more than 5 hours before it should have been. I'm living in Houston so i'm on central time. The photo should have deleted at 11:23 AM central so i'm guessing this is a timezone issue. If it is how can add a timezone on to the date, if it isn't what could be the problem?

Solution

  • It could be a time zone issue if you dB is not on the same timezo e you are on.

    Is it imperative that the data is being cleaned every hour rather than every day.

    You might get more joy out of using the date part functionality getting just the day out. This way you get rid of the time stamp completely

    So 

    Delete from photos where created_at < ((date(now()) - INTERVAL 1 DAY );