MySQL CURRENT_TIMESTAMP compare

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

MySQL CURRENT_TIMESTAMP compare

Post 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.
maxx99
Forum Contributor
Posts: 142
Joined: Mon Nov 21, 2011 3:40 am

Re: MySQL CURRENT_TIMESTAMP compare

Post 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)
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL CURRENT_TIMESTAMP compare

Post 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'];
			}
maxx99
Forum Contributor
Posts: 142
Joined: Mon Nov 21, 2011 3:40 am

Re: MySQL CURRENT_TIMESTAMP compare

Post by maxx99 »

Check out

Code: Select all

SELECT * FROM list WHERE timestamp > DATE_SUB( NOW(), INTERVAL 24 HOUR) ORDER BY type, name 
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL CURRENT_TIMESTAMP compare

Post 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.
maxx99
Forum Contributor
Posts: 142
Joined: Mon Nov 21, 2011 3:40 am

Re: MySQL CURRENT_TIMESTAMP compare

Post by maxx99 »

Could you post the schema of your table and an example of one of your records?
Hermit TL
Forum Commoner
Posts: 69
Joined: Mon Nov 21, 2011 12:16 am

Re: MySQL CURRENT_TIMESTAMP compare

Post 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!
maxx99
Forum Contributor
Posts: 142
Joined: Mon Nov 21, 2011 3:40 am

Re: MySQL CURRENT_TIMESTAMP compare

Post by maxx99 »

Good to hear :) good luck!
Post Reply