Jump to content

What's best way to calculate between two dates


simcoweb

Recommended Posts

I have a classified ads system under development. Two of the fields in the database are:

 

date_created

date_expired

 

I want to populate both of these at the time the ad is posted. The date created value is derived from:

 

$date_created=now();

 

For the date expired I have this:

 

$date_expired = mktime(0, 0, 0, date("m"), date("d")+7, date("y"));

 

Which takes today's date plus 7 days (ads expire in 7 days) and inserts this date into the database.

 

Now, in the query, i'm fuzzy on how I would pull all ads the aren't expired.

 

 

It will do just what it says, pull any ads where now() is after it was created and before it expires.

 

Actually, it pulls them where now() >= created and now() <= expires.

 

"BETWEEN A and B" includes the values A and B in the selection

Oh, and I did want to mention that your code will make everything expire at midnight (like the first second of every day)

 

I don't know if it will be a problem in this situation, but say someone signs up at 11:30 PM. They'll only get 30 minutes of their day.

 

You could either set the expiration using date_expired = DATE_ADD(NOW(), INTERVAL 7 DAY)

 

or on the query I gave you do this:

 

SELECT * FROM ads

WHERE [red]DATE(NOW())[/red] BETWEEN date_created AND date_expired

If the duration is always 7 days then you don't need the expiry date, it can always be calculated when required from the creation date. Just like you don't need to store a person's age if you have the date of birth.

The only problem with that is that it is SO hard to say ALWAYS in business. If you think that in the future your boss may come to you and say, "we need to extend so and so's ads for one more day because..." then stick with your current approach.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.