CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 23959

MySQL - group by a timestamp range

I want to monitor actions of varying types on a site, am gonna use logging in as the example.

user_logins table:

user_id     name            when_logged
-------     ----            -----------
smallint    varchar(128)    timestamp

So, to see each user who has logged on today (and how many times) I use:

select name, count(name) from user_logins 
where when_logged between '2012-11-17 00:00:00' AND '2012-11-17 23:59:59' 
group by name;

However, I want to do that for the last 14 days, each day seperately...

Is it possible to do this in one query as opposed to 14 queries by changing the date?

Upvotes: 1

Views: 871

Answers (2)

Nesim Razon
Nesim Razon

Reputation: 9794

select date(when_logged) as datelogged, name, count(name) 
from user_logins
where when_logged >= date_sub(current_timestamp, INTERVAL 14 day) 
group by name, date(when_logged);

Upvotes: 1

juergen d
juergen d

Reputation: 204746

 select name, count(when_logged) 
 from user_logins 
 where when_logged >= curdate() - interval 14 day
 group by name, date(when_logged)

Upvotes: 1

Related Questions