# Fastest way to count how many dates' ranges cover each date from series

I have a table (in PostgreSQL 9.4) that looks like this:

``````CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
(1, '2018-01-01', '2018-01-31'),
(1, '2018-01-01', '2018-01-05'),
(1, '2018-01-03', '2018-01-06'),
(2, '2018-01-01', '2018-01-01'),
(2, '2018-01-01', '2018-01-02'),
(3, '2018-01-02', '2018-01-08'),
(3, '2018-01-05', '2018-01-10');
``````

Now I want to calculate for the given dates and for every kind, into how many rows from `dates_ranges` each date falls. Zeros could be possibly omitted.

Desired result:

``````+-------+------------+----+
|  kind | as_of_date |  n |
+-------+------------+----+
|     1 | 2018-01-01 |  2 |
|     1 | 2018-01-02 |  2 |
|     1 | 2018-01-03 |  3 |
|     2 | 2018-01-01 |  2 |
|     2 | 2018-01-02 |  1 |
|     3 | 2018-01-02 |  1 |
|     3 | 2018-01-03 |  1 |
+-------+------------+----+
``````

I've come up with two solutions, one with `LEFT JOIN` and `GROUP BY`

``````SELECT
kind, as_of_date, COUNT(*) n
FROM
(SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates
LEFT JOIN
dates_ranges ON dates.as_of_date BETWEEN start_date AND end_date
GROUP BY 1,2 ORDER BY 1,2
``````

and one with `LATERAL`, which is slightly faster:

``````SELECT
kind, as_of_date, n
FROM
(SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates,
LATERAL
(SELECT kind, COUNT(*) AS n FROM dates_ranges WHERE dates.as_of_date BETWEEN start_date AND end_date GROUP BY kind) ss
ORDER BY kind, as_of_date
``````

I'm wondering is it any better way to write this query? And how to include pairs date-kind with 0 count?

In reality there is a few distinct kinds, period of up to five years (1800 dates), and ~30k rows in `dates_ranges` table (but it could grow significantly).

There are no indexes. To be precise in my case it's a result of subquery, but I've wanted to limit question to one issue, so it's more general.

• What do you do if you the ranges in the table are non-overlapping or touching. For instance if you have a range where (kind,start,end) = `(1,2018-01-01,2018-01-15)` and `(1,2018-01-20,2018-01-25)` do you want to take that into account when determining how many overlapping dates you have? Commented May 30, 2018 at 17:32
• I'm also confused why your table is small? Why isn't `2018-01-31` or `2018-01-30` or `2018-01-29` in it when the first range has all of them? Commented May 30, 2018 at 17:33
• @EvanCarroll dates in `generate_series` are external parameters - they don't necessarily cover all ranges in `dates_ranges` table. As for the first question I suppose I don't understand it - rows in `dates_ranges` are independent, I don't want to determine overlaping. Commented Jun 1, 2018 at 7:47

The following query also works if "missing zeros" are OK:

``````select *
from (
select
kind,
generate_series(start_date, end_date, interval '1 day')::date as d,
count(*)
from dates_ranges
group by 1, 2
) x
where d between date '2018-01-01' and date '2018-01-03'
order by 1, 2;
``````

but it's not any faster than the `lateral` version with the small dataset. It might scale better though, as no join is required, but the above version aggregates over all the rows, so it may lose out there again.

The following query tries to avoid unnecessary work by removing any series that don't overlap anyway:

``````select
kind,
generate_series(greatest(start_date, date '2018-01-01'), least(end_date, date '2018-01-03'), interval '1 day')::date as d,
count(*)
from dates_ranges
where (start_date, end_date + interval '1 day') overlaps (date '2018-01-01', date '2018-01-03' + interval '1 day')
group by 1, 2
order by 1, 2;
``````

-- and I got to use the `overlaps` operator! Note that you have to add `interval '1 day'` to the right as the overlaps operator considers time periods to be open on the right (which is fairly logical because a date is often considered to be a timestamp with time component of midnight).

• Nice, I didn't know `generate_series` can be used like that. After a few tests I have following observations. Your query indeed scales really well with the selected range length - theret is practically no difference between 3 years and 10 years period. However for shorter periods (1 year) my solutions are faster - I'm guessing that the reason is that there are some really long ranges in `dates_ranges` (like 2010-2100), that are slowing down Your query. Limiting `start_date` and `end_date` inside the inner query should help though. I need to make a few more tests. Commented May 30, 2018 at 15:24

And how to include pairs date-kind with 0 count?

Build a grid of all combinations then `LATERAL` join to your table, like this:

``````SELECT k.kind, d.as_of_date, c.n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS  JOIN (
SELECT d::date AS as_of_date
FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
) d
CROSS  JOIN LATERAL (
SELECT count(*)::int AS n
FROM   dates_ranges
WHERE  kind = k.kind
AND    d.as_of_date BETWEEN start_date AND end_date
) c
ORDER  BY k.kind, d.as_of_date;
``````

Should also be as fast as possible.

I had `LEFT JOIN LATERAL ... on true` at first, but there is an aggregate in the subquery `c`, so we always get a row and can use `CROSS JOIN` as well. No difference in performance.

If you have a table holding all relevant kinds, use that instead of generating the list with subquery `k`.

The cast to `integer` is optional. Else you get `bigint`.

Indexes would help, especially a multicolumn index on `(kind, start_date, end_date)`. Since you are building on a subquery, this may or may not be possible to achieve.

Using set-returning functions like `generate_series()` in the `SELECT` list is generally not advisable in Postgres versions before 10 (unless you know exactly what you are doing). See:

If you have lots of combinations with few or no rows, this equivalent form may be faster:

``````SELECT k.kind, d.as_of_date, count(dr.kind)::int AS n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS JOIN (
SELECT d::date AS as_of_date
FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
) d
LEFT   JOIN dates_ranges dr ON dr.kind = k.kind
AND d.as_of_date BETWEEN dr.start_date AND dr.end_date
GROUP  BY 1, 2
ORDER  BY 1, 2;
``````
• As for set-returning functions in the `SELECT` list - I've read that it is not advisable, however it looks like it works just fine, if there is only one such function. If I am sure that there will be only one, could something go wrong? Commented Jun 1, 2018 at 8:00
• @BartekCh: A single SRF in the `SELECT` list works as expected. Maybe add a comment to warn against adding another one. Or move it to the `FROM` list to begin with in older versions of Postgres. Why risk complications? (That's also standard SQL and won't confuse people coming from other RDBMS.) Commented Jun 4, 2018 at 15:26

# Using the `daterange` type

PostgreSQL has a `daterange`. Using it is pretty simple. Starting with your sample data we move to use the type on the table.

``````BEGIN;
ALTER TABLE dates_ranges ADD COLUMN myrange daterange;
UPDATE dates_ranges
SET myrange = daterange(start_date, end_date, '[]');
ALTER TABLE dates_ranges
DROP COLUMN start_date,
DROP COLUMN end_date;
COMMIT;

-- Now you can create GIST index on it...
CREATE INDEX ON dates_ranges USING gist (myrange);

TABLE dates_ranges;
kind |         myrange
------+-------------------------
1 | [2018-01-01,2018-02-01)
1 | [2018-01-01,2018-01-06)
1 | [2018-01-03,2018-01-07)
2 | [2018-01-01,2018-01-02)
2 | [2018-01-01,2018-01-03)
3 | [2018-01-02,2018-01-09)
3 | [2018-01-05,2018-01-11)
(7 rows)
``````

I want to calculate for the given dates and for every kind, into how many rows from dates_ranges each date falls.

Now to query it we reverse the procedure, and generate a date series but here's the catch the query itself can use the containment (`@>`) operator to check that the dates are in range, using an index.

Note we use `timestamp without time zone` (to stop DST hazards)

``````SELECT d1.kind, day::date, count(d2.kind)
FROM dates_ranges AS d1
CROSS JOIN LATERAL generate_series(
lower(myrange)::timestamp without time zone,
upper(myrange)::timestamp without time zone,
'1 day'
) AS gs(day)
INNER JOIN dates_ranges AS d2
ON d2.myrange @> day::date
GROUP BY d1.kind, day;
``````

Which is the itemized day-overlaps on the index.

As a side bonus, with the daterange type you can stop insertions of ranges that overlap with others using an `EXCLUDE CONSTRAINT`

• Something is wrong with Your query, it looks like it is counting rows multiple times, one `JOIN` too much I guess. Commented Jun 1, 2018 at 16:44
• @BartekCh no you have overlapping rows, you can get around this by removing the overlapping ranges (suggested) or using `count(DISTINCT kind)` Commented Jun 1, 2018 at 17:33
• but I want overlapping rows. For example for kind `1` date `2018-01-01` is within first two rows from `dates_ranges`, but Your query gives `8`. Commented Jun 1, 2018 at 18:59
• or using `count(DISTINCT kind)` did you add the `DISTINCT` keyword there? Commented Jun 1, 2018 at 19:10
• Unfortunately with `DISTINCT` keyword it still doesn't work as expected. It counts distinct kinds for every date, but I want to count all rows of each kind for every date. Commented Jun 1, 2018 at 20:34