CODEWITHSUNDEEP
mysqlsql
lpinto.eu
lpinto.eu

Reputation: 2127

Mysql count record by day on a time interval

I have a table of events (now around 2 or 3 million), with start and end dates (spread for several years). I Want to know how many events exist every day. Not started by day, but happening each calendar day. E.g.

| name | start      | End        |
| Ev1  | 2019/01/01 | 2019/01/03 |
| Ev2  | 2019/01/02 | 2019/01/04 |
| Ev3  | 2019/02/22 | 2019/02/23 |

Expected result:

| day        | # |
| 2019/01/01 | 1 |
| 2019/01/02 | 2 |
| 2019/01/03 | 2 |
| 2019/01/04 | 1 |
| 2019/01/05 | 0 |
|     ...    | 0 |
| 2019/02/22 | 1 |
| 2019/02/23 | 1 |

Upvotes: 1

Views: 168

Answers (3)

Alex
Alex

Reputation: 2337

try something like this, you need a date generator.

select
    d.dte, count(e.start) as cnt
from 
(
select dte from
    (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) dte from
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
Where dte between '2019-01-01' and '2020-12-31'
) d
inner join events e
     on e.start <= d.dte and d.dte <= e.end
group by d.dte

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30665

It would be feasible to have calendar table for this kind of information. Then,

select c_date, count(*)
from calendar c
inner join events on e.start <= c_date and c_date <= e.end
group by c_Date;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270893

This is tricky. In MySQL, start with the dates and use a correlated subquery:

select d.dte, count(e.start) as cnt
from (select date('2019-01-01') as dte union all
      select date('2019-01-02') as dte union all
      select date('2019-01-03') as dte union all
      select date('2019-01-04') as dte
     ) d left join
     events e
     on e.start <= d.dte and d.dte <= e.end
group by d.dte
order by d.dte;

Upvotes: 0

Related Questions