Re: [PHP] PHP <-> MySQL Timestamp Storage and Comparison From: php3 <email protected>
Date: 09/30/00

Addressed to: "Gary Singleton" <gsinglet2 <email protected>>
              php-general <email protected>

** Reply to note from "Gary Singleton" <gsinglet2 <email protected>> Fri, 29 Sep 2000 16:50:47 -0700
>
> I am trying to find the best way of storing time values in MySQL so I can=
> compare them later.

DateTime and Timestamp.

See:

http://www.mysql.com/documentation/mysql/commented/manual.php?section=Column_types

> if (($date_updated - $date_original) <=3D ($day * 3) && ($date_current -=
> $date_updated) <=3D ($day * 3)) {
> echo "new";
> }
> elseif (($date_updated - $date_original) > ($day * 3) && ($date_current -=
> $date_updated) < ($day * 3)) {
> echo "updated";
>}
>else {
> echo "old";
>}

How about doing it with MySQL?

SELECT IF( ADDDATE( date_updated, INTERVAL 3 DAYS ) < NOW(), 'old',
           IF( ADDDATE( date_original, INTERVAL 3 dAYS) < NOW(),
               'updated', 'new' )) AS age_tag;

This returns a value named 'age_tag' that is set to:

   if record was created in the last 3 days : new
   if it was created earlier, but updated in the last 3 days: updated
   else: old.

In MySQL the IF( construct works like:

IF( conditional_statement, return_if_true, return_if_false );

You should also look at:

http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_functions>
 

It is all in the manual...

Rick Widmer
Internet Marketing Specialists
www.developersdesk.com 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-general-unsubscribe <email protected>
For additional commands, e-mail: php-general-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>