What's best way to calculate between two dates

[simcoweb]

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.

 

 

[artacus]

SELECT * FROM ads

WHERE NOW() BETWEEN date_created AND date_expired

[simcoweb]

Will that pull the ads from both date created and date expired as well as the in between?

[artacus]

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

 

 

[simcoweb]

Ok, cool. Thanks!

[Barand]

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

[simcoweb]

Barand, thanks for the clarification on that. I 'assumed' that was the case but thanks for validating it.

[artacus]

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

[Barand]

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.

[simcoweb]

Ok, so what you're saying is I really don't need the expiration date?

[Barand]

IF the "always seven days" holds true.

 

SELECT ... WHERE NOW() BETWEEN create_date AND create_date + INTERVAL 7 DAYS

[artacus]

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.