CODEWITHSUNDEEP
phpmysqlsql
Logi
Logi

Reputation: 115

MySQL Query that only runs within 72 hours

I currently have a script that runs news, from a game, and displays stats etc. One of the queries just counts the number of items in the database and displays it. The database it is counting has a column named "timestamp" and it is in the unix timestamp format (number of seconds).

I want the query only to add up the number of instances in the database that has occurred within the 72hours prior to the query being ran. (so If i run it at 10am 8/8/14 I want it to count the records backwards 72 hours all the way to 10am 8/5/2014).

I've tried to write my query to accomplish this but I do not know enough about how to write queries to figure out how I should arrange it. Here is what I have so far which counts all records in the entire database regardless of time:

$result2 = mysql_query("SELECT COUNT(xxxxx) FROM xxx WHERE xxxxx= '$row[3]'")
           or die("Could not select the table");

How can I modify the above query to filter out anything that is no within the last 72 hours. Remember my data base does have a column called "timestamp" which is in the unix timestamp format. Thanks for the help this has stumped me for a while now!

Upvotes: 0

Views: 1850

Answers (3)

engvrdr
engvrdr

Reputation: 541

You can get time timestamp with strtotime function

And your code would be like

$time = strtotime ("-3 DAY");
$result2 = mysql_query("SELECT COUNT(xxxxx) FROM xxx WHERE xxxxx= '$row[3]' AND timestamp > $time")
           or die("Could not select the table");

Upvotes: 0

kainaw
kainaw

Reputation: 4334

If the timestamp field is a proper time column and not an integer, you can use MySQL to do this for you. 72 hours is 3 days. The datediff function returns the number of days between two date/time fields.

select * from xxx where datediff(now(),timestamp)<=3;

Upvotes: 1

Marc B
Marc B

Reputation: 360702

WHERE yourtimefield > (now() - INTERVAL 72 HOUR)

Upvotes: 3

Related Questions