mySQL Date

[nikomou]

Hello,

I'm using the Date format in my database in the format e.g.

2008-02-19
YYYY-MM-DD

And i want to do something like this:

If date is less than 1 month old, echo "NEW".

Thanks for your help

[boogyman]

why not use unix time as an integer field, then check that against the current time minus 1 month



$now = time();

$lastMonth = strtotime("-1 Month", $now);

$thread = THREAD_TIME_IN_UNIX_TIMESTAMP;

if($thread < $lastMonth)
{
$status = "NEW";
}

[BYK]

I think there was a "DATE_DIFF" function in MySQL. Please have a look at the "Date and Time Functions" section in the manual. You can find it via any search engine ;)

[phpsales]

Please use the MySQL DATE_SUB() module.

Example SQL:
-- returns NEW or OLD in `age` column as requested
SELECT IF('2008-11-19'>DATE_SUB(NOW(), INTERVAL 1 MONTH),'NEW','OLD') as `age`;

Example SQL in PHP:

$sql = sprintf("SELECT IF('%s'>DATE_SUB(NOW(), INTERVAL 1 MONTH),'NEW','OLD') as `age`;", $date);


Reference:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add