WebPaws.com

MySQL time and date functions are well documented (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html), but the applications for specific needs are not. I would like to share a tidbit about comparing two database date entries, or one entry to the current time and then comparing the difference.

The application is for an auction website, where I am comparing the current time to either the ending time date or the beginning time date to determine how many hours the current time is from either of those timestamps.

My PHP code sets a MySQL variable to insert into the SQL statement to compare the end or beginning timestamp.


//In PHP, depending on the search query, I set the SQL to compare the beginning timestamp or the ending timestamp.

if($cat==1){$catsql="TIMESTAMPDIFF(HOUR,StartTime,Now()) < '24'";}
elseif($cat==2){$catsql="TIMESTAMPDIFF(HOUR,Now(),EndTime) < '24'";}
else{$catsql="";}

In the SQL code, I am finding the difference in Hours from the current time Now() to either the EndTime or the StartTime timestamps. If less then 24 hours, it allows a condition to occur in the SQL statement to select specific records meeting that condition.

Keep in mind that the time date format is important and should be in the following format for the above MySQL statements to work:


$currenttime = date("Y-m-d H:i:s"); //PHP timestamp consitant with MySQL


Digg

Stumble Upon

Del.icio.us

Buzz