Page 1 of 1

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

Posted: Wed Jun 12, 2002 9:17 pm
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?

Posted: Thu Jun 13, 2002 3:40 am
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