Page 1 of 1

MySQL CURRENT_TIMESTAMP compare

Posted: Wed Nov 23, 2011 1:03 am
by Hermit TL
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.

Re: MySQL CURRENT_TIMESTAMP compare

Posted: Wed Nov 23, 2011 1:28 am
by maxx99
You can do it on MySQL, check out:
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

Posted: Wed Nov 23, 2011 1:46 am
by Hermit TL
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

Posted: Wed Nov 23, 2011 1:48 am
by maxx99
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

Posted: Wed Nov 23, 2011 2:05 am
by Hermit TL
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

Posted: Wed Nov 23, 2011 2:07 am
by maxx99
Could you post the schema of your table and an example of one of your records?

Re: MySQL CURRENT_TIMESTAMP compare

Posted: Wed Nov 23, 2011 2:16 am
by Hermit TL
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!

Re: MySQL CURRENT_TIMESTAMP compare

Posted: Wed Nov 23, 2011 2:17 am
by maxx99
Good to hear :) good luck!