CODEWITHSUNDEEP
phpmysqlsqlcount
Deniz Zoeteman
Deniz Zoeteman

Reputation: 10121

Count number of rows with activity within the last 7 days

Basically, I have a field in my table that shows when the latest activity was in a regular unix timestamp.

What I need is a way to count the rows that have had activity in the past 7 days.

Upvotes: 1

Views: 242

Answers (2)

Adam Wenger
Adam Wenger

Reputation: 17570

SELECT COUNT(1)
FROM yourTable
WHERE DATEDIFF(CURDATE(), FROM_UNIXTIME(yourDateColumn)) <= 7

Upvotes: 1

goat
goat

Reputation: 31834

select count(*)
  from someTable
 where lastActivity > UNIX_TIMESTAMP(NOW() - INTERVAL 7 day)

Note that this is 7 days worth of seconds.

If you want activity of last 7 calendar days

select count(*)
  from someTable
 where lastActivity > UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 day)

Upvotes: 6

Related Questions