CODEWITHSUNDEEP
phpdatabasetimestamp
Dan
Dan

Reputation: 41

Query to select records from a database that were created within the last 24 hours

I am wondering how to query to the database to show results that have been submitted in the last 24 hours.

In the table, topics, I have a collumn set up called start_date which uses timestamps. How would I query the database to find the topics created in the last 24 hours?

Basically, I want to do something like this, but not sure how to use WHERE:

 $query = mysql_query("SELECT * FROM topics WHERE start_date = 'LAST 24 HOURS?'");

Thanks :)

Upvotes: 0

Views: 698

Answers (3)

pconcepcion
pconcepcion

Reputation: 5641

You can try with something like this;

$query = mysql_query("SELECT * FROM topics WHERE start_date = DATE_ADD(NOW(), INTERVAL -1 DAY)");

Edited: the math of NOW() - 60*60*24 was wrong. Sorry.

Upvotes: 1

icaptan
icaptan

Reputation: 1535

... where start_date > @date

and your date should be mention to time 1 day before now...

Jimmy's query shall run well too !

Upvotes: 0

Jimmy Sawczuk
Jimmy Sawczuk

Reputation: 13614

Try this:

$query = mysql_query("SELECT * FROM topics WHERE start_date >= ".time() - 24 * 3600);

This is asking your database for all topics with a start date timestamp greater than or equal to the timestamp 24 hours ago. Note that you can use a DATETIME column too, with only a slight modification:

$query = mysql_query("SELECT * FROM topics WHERE start_date >= '".date("Y-m-d H:i:s", time() - 24 * 3600)."'");

Upvotes: 0

Related Questions