i need some help with processing dates, should be easy...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

i need some help with processing dates, should be easy...

Post by chris12295 »

my goal is to have an online ad site and the ads are paid for for a certain number of days. after that number of days they will be deleted from the database. i need somthing to calculate the days.


i need to add 2 fields i think. i want to add a field with the current date and then add a field to calculate the date a variable amount of days after the start date. how can i do it?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

you may instert a startDate and a endDate field in your record.
Most databases have a set of date-functions. With mysql you may use something like that

Code: Select all

$sqlCurrent = "INSERT into adsTable (....,startDate, endDate) VALUES(...,$startdate, $startdate+INTERVAL $paidDays DAY)";
this will store two absoulte time/date values. The select-query would be

Code: Select all

$sqlCurrent = "SELECT <fields> from adsTable WHERE startDate <= NOW() AND endDate >= NOW()";

$sqlTImedOut = "SELECT <fields> from adsTable WHERE endDate < NOW()";
You may also store the fields startDate(DATE), paidDays(INT) to build the query

Code: Select all

$sqlCurrent = "SELECT t.<fields> from adstable t WHERE t.startDate<= NOW() AND DATE_ADD(t.startDate,INTERVAL t.paidDays DAY) >= NOW()";

$sqlTimedOut = "SELECT t.<fields> from adstable t WHERE DATE_ADD(t.startDate,INTERVAL t.paidDays DAY) < NOW()";
mysql Date and Time Functions
hoping it's helpful
Volker
Post Reply