0

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?

brian Scroggins
  • 2,701
  • 3
  • 17
  • 17
  • 1 day is 1 day. `... < (NOW() - INTERVAL 1 DAY)` will always mean *older than 1 day*, in every timezone. So either the value in `created_at` is coming from a different server with a different timezone than the SQL server, or the problem must be something else. – Marcello Mönkemeyer Sep 15 '15 at 18:07
  • This is not the case, for example if created_at is 5:30 PM and the event runs the next hour at 6:30 PM created_at is 23 hours older than (6:30PM - 1 day) which will reference 6:30 the day before. The math is correct – brian Scroggins Sep 15 '15 at 18:42
  • This makes absolutely no sense. If you crate something at, let's say, 1PM and run the the cronjob 1 hour later, at 2PM, then 24-(2-1) = 23, *not* -23, so it's **newer** than (2PM - 1 day). The math is still correct, though. What I was trying to say is that it makes no difference if you subtract 24 hours within CEST, EEST, WEST or whatever timezone - 24 hours are 24 hours, in every timezone. – Marcello Mönkemeyer Sep 15 '15 at 19:25
  • 2PM on a Tuesday is 23 hours younger that 1PM on a Wednesday is it not? If you add 23 hours to that 2 PM it becomes 1PM Wednesday which is the concept I was getting at. Nonetheless you are right about the timezone and I appreciate it. I discovered that my mysql server was outputting time in UTC GMT instead of my timezone which was the problem – brian Scroggins Sep 15 '15 at 21:06
  • Regarding the terminology: 1PM on a Tuesday is 1 day *older* than 1PM on a Wednesday, because it lays in the *past* relatively to that Wednesday. The other way around, 1PM on a Wednesday is 1 day *younger* than 1PM on a Tuesday, because it lays in the *future* relatively to that Tuesday. But whatever, your problem was fixed, and that's the main thing. :) – Marcello Mönkemeyer Sep 16 '15 at 08:00
  • True you got me there man,messed up my terms but you know what I meant :) but thanks for the assistance – brian Scroggins Sep 16 '15 at 08:34

1 Answers1

1

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 );
Jacques Ramsden
  • 801
  • 5
  • 20
  • This is the correct answer. After doing some investigation I found that I was converting the time to my local time while my mysql server was using GMT timezone so I was 5 hours behind. – brian Scroggins Sep 15 '15 at 19:01