CODEWITHSUNDEEP
mysqlsqldategroup-by
shijie xu
shijie xu

Reputation: 2097

Select count group by day of the dateTime

I have a table monitor and one of its field is date of Date type. A sample record is like 

id  |  date                    |  other fields.
1      2019-03-01 20:00:00 
2      2019-03-01 20:30:00 
3      2019-03-02 20:00:00
4      2019-03-01 10:00:00

Now, i want to count the number of records in the last five days, and return a map like: 

day|  count
2019-03-01    3
2019-03-02    1

My sql is like: 

select day(date),count(*) from monitor group by day(date);

So how should i construct in mysql? Thanks. The problem for me here is group by day of Date field,.

Upvotes: 1

Views: 3720

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would use date() and be very careful about the where clause. Do not use now(), because it also has a time component.

Instead:

select date(date), count(*)
from monitor
where date >= curdate() - interval 5 day
group by date(date);

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use DATE_FORMAT function.

select DATE_FORMAT(date,'%Y-%m-%d'),count(*) 
from monitor 
WHERE date between DATE_SUB(NOW(), - INTERVAL 5 DAY) AND NOW()
group by DATE_FORMAT(date,'%Y-%m-%d');

Upvotes: 2

Related Questions