I'm having some problems trying to figure out exactly how to do this. I have a value stored in my MySQL databse as TIMESTAMP.
After pulling the time stamp from the database, how would I compare it to the current time? Or is there a way to do using a MySQL query without pulling the time stamp into a variable.
I need to know whether or not the TIMESTAMP in the MySQL database is older than 24 hours or not.
MySQL CURRENT_TIMESTAMP compare
Moderator: General Moderators
Re: MySQL CURRENT_TIMESTAMP compare
You can do it on MySQL, check out:
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
e.g. DATE_SUB
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
e.g. DATE_SUB
Code: Select all
WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)Re: MySQL CURRENT_TIMESTAMP compare
Thank you; that looks like just what I need, but I can't get it working. MySQL is not my strong point. Is there something wrong with my query? Both the greater than and less than signs in the following query return no results. I'm not sure what I did wrong.
Code: Select all
$query = "SELECT * FROM list ORDER BY type, name WHERE timestamp < DATE_SUB( NOW(), INTERVAL 24 HOUR)";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
echo $row['name'];
}
Re: MySQL CURRENT_TIMESTAMP compare
Check out
Code: Select all
SELECT * FROM list WHERE timestamp > DATE_SUB( NOW(), INTERVAL 24 HOUR) ORDER BY type, name Re: MySQL CURRENT_TIMESTAMP compare
hmmm. same thing; no results. (Just tried it a bunch of different ways; no results every time.) Does it matter how the TIMESTAMP was set in MySQL? I didn't send the time stamp in the query that added the value to the database. I set the default for value for the timestamp column in MySQL as CURRENT_TIME and omitted it from the querys which causes it to update on each query to the current time stamp.
Re: MySQL CURRENT_TIMESTAMP compare
Could you post the schema of your table and an example of one of your records?
Re: MySQL CURRENT_TIMESTAMP compare
Ah ha! My bad, I was sending the queries to the wrong the table. Thank you for the assistance (I have a habit of overlooking the simplest things.); it's going to take me a bit to fix what I messed up and run another test. If I don't post in this thread again, your query is working.
Thanks!
Thanks!
Re: MySQL CURRENT_TIMESTAMP compare
Good to hear
good luck!