Check if date has passed from mysql timestamp?

ID:1181634
 
Feb 27 2013, 4:41 pm
(See the best response by Pirion.)
Code:


Problem description:
I have a marketplace on my game...and i was wondering how to determine if a date has passed say... 7 days after an item is listed i want it to return to the seller.

http://puu.sh/29zCY <-- date.

Feb 27 2013, 5:34 pm
You'll want to compare that date to the date of the day of the check.

This is how I would get the actual date, I've put the format to mimic the MySQL entry you had.
time2text(world.timeofday,"YYYY-MM-DD hh:mm:ss")
Feb 27 2013, 6:07 pm
Best response
The following query would return the ids of anything that has a date of seven days ago.

The function to look at is the MySQL DATE_ADD() which is referenced pretty well here: http://www.w3schools.com/sql/func_date_add.asp

The reason we are doing this in to the NOW() rather than the DATE column is because by converting the function we are able to preserve any indexes and utilize them to keep the query efficient if they are defined on the table.

SELECT ID
FROM TABLE
WHERE DATE <= DATE_ADD(NOW(),DAY,-7)


I do not recommend ATHK's answer because the database time and server time(s) if hosted in different locations or not default can be different, and is also not as simple in my opinion.
Feb 27 2013, 6:10 pm
In response to Pirion
Whilst that is true, depends if the server is only hosted on the one machine, but yes you cater for all needs.

I actually never thought of doing it via MySQL ....