now() and an hour from now()

Anon

I am runing php from the command line as a cron job. I have a email which is sheduled to go out sometime during the day so it is stored as Sendtime in the database (MySQL).

The cron job will run hourly, so I need all the emails that are scheduled for the current hour, but I am now sure how to write the sql statment.

something like:
select * from Mailqueue where (now() + hour(1)) < Sendtime and sent = '0';

this obvioulsy won't work, but I think it explains what I want

thanks

Anon

Try this out

$now = time(); //
$one_hour_later = $now + 3600;
$select = "select * from Mailqueue where
(Sendtime > $one_hour_later) and (sent = '0')

I hope this helps out, it just get's the current time then adds 3600 to it, to give you 60 minutes from the current time.

Mike J

Anon

MikeJ is right, and I'm not trying to steal his thunder, but there is a little trick that helps when you need to do this kind of thing. It's just a different way of looking at MikeJ's solution:

$timestamp = time() + 60; //plus one minute
$timestamp = time() + (6060); //plus one hour
$timestamp = time() + (606024); //plus one day
$timestamp = time() + (6060247); //plus one week
$timestamp = time() + (606024752); //plus one year

etc, etc. If you do it this way it's a lot easier to understand and remember what the numbers where for.

Hope that helps,
Jamie.

Anon

the easiest way is to do it all in mysql:

select * from Mailqueue where (unix_timestamp(now() + 3600) > unix_timestamp(Sendtime)) and sent = '0';

although you probably want to subtract 3600 to get everything in the last hour, not the next hour.

I think the way you have it will send out mails scheduled for 12 noon at 11 am. Since it runs hourly, you way not want the 3600 at all, just be sure to set the sent flag on send.

the other two ways suggested won't really work becuase mysql stores dates in timestamp form:
20001215000000
which you can't add 3600 seconrds to very easily. You would need to convert to unixtime first.

Anon

You've reminded me of another trick I use: store the timestamp returned from the time() function as an integer in the database rather than a 'datetime' or 'datestamp' which are notoriously awkward to use. You still have all the functionality but none of the hassle.

Jamie