CODEWITHSUNDEEP
phpmysqlsqldatetimetimestamp
MultiDev
MultiDev

Reputation: 10659

How to get all timestamps for a specific date? PHP

I have rows in a MySQL database, and each has a timestamp value. (ie 1340296188)

I am trying to find all entries for the last 7 days. I know how to do a query to find all WITHIN the last 7 days, something like:

$lastweek = time() - 604800;
$results = mysql_query("SELECT * FROM table WHERE timestamp > $lastweek");

However, I need to be able to get each day seperately. How would I do this query? I don't know how to find from the above results which ones are for which specific date.

Upvotes: 1

Views: 525

Answers (2)

andrewsi
andrewsi

Reputation: 10732

You're pretty much there already; you just need to parse the timestamp field in $results.

A day is (60 * 60 * 24) seconds, so you can divide each row's timestamp field by 86400, turn it into an integer, and the result is the number of days ago that this entry occurred.

For each row in your dataset:

$daysAgo = int($row["timestamp"] / 86400);

Upvotes: 2

John Conde
John Conde

Reputation: 219934

I believe there is more then one way to do this. Here's one:

SELECT * 
FROM table 
WHERE timestamp > CURRENT_DATE - INTERVAL 1 WEEK
GROUP BY DAY(timestamp)

Upvotes: 4

Related Questions