Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the maximum sum() of applicable overlapping ranges. A simple example:

create table event (
  event_id int primary key,
  event_type_id int not null,
  period tstzrange not null,
  quantity int not null

insert into event (event_id, event_type_id, period, quantity) values
(1, 1,'[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]',1),
(2, 1,'[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]',1),
(3, 1,'[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]',1),
(4, 2,'[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]',1);

Given a query with the following clauses:

select ... 
where event_type_id = 1
and period && '[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange 
group by event_type_id

The desired result would be: 3, i.e. the maximum sum(quantity) where the ranges of the same event_type_id overlap within a given timestamp range.

  • All ranges are inclusive, and there are no constraints other than the PK and no indexes, since I wanted to keep the example simple. – Rob Feb 3 '15 at 17:22

The task as I understand it

Pick rows from a table where the period overlaps with a given time frame. Determine distinct ranges of overlapping periods within that set and return the greatest sum(quantity) from any range.

Requires Postgres 9.2+, since there are no range types in older versions.


  • "Overlapping" is meant in a cascading manner, like tiles on a traditional roof: those are "overlapping" (rainproof), though the highest tile does not directly overlap with the lowest.

  • All values in period have inclusive bounds ([]). Else you have to adjust for exclusive bounds. (The range of the input parameter can still have arbitrary bounds.)

  • We filter for exactly one event_type_id. Else you have to add PARTITION BY event_type_id to the window definition.

  • quantity is an integer. Else you have to adjust for the type in calculations.

  • Quantities for overlapping periods are counted fully, even if parts of the period are outside your given time frame.

  • Even works for duplicates on (event_type_id, period).

Best performance with a single subquery

This should be dynamite.

SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
   SELECT lower(period) AS p_start
        ,(sum(quantity)                      OVER w)::int AS running_sum
        , lead(lower(period), 1, 'infinity') OVER w
                        > max(upper(period)) OVER w AS range_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange 
   WINDOW w AS (ORDER BY lower(period))
   ) sub
WHERE  range_end

All three window functions in the subquery can use the same window. This avoids additional sort operations and should be fastest.

Verbose CTE variant with more explanation

Same query, just more verbose and slower, since CTEs materialize derived tables and pose as optimization barriers.

WITH cte1 AS (
   SELECT quantity
        , lower(period) AS p_start
        , upper(period) AS p_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
,    cte2 AS (
   SELECT (sum(quantity)               OVER w)::int AS running_sum
        , lead(p_start, 1, 'infinity') OVER w               -- next start ..
                          > max(p_end) OVER w AS range_end  -- .. after last end
        , p_start, p_end
   FROM   cte1
   WINDOW w AS (ORDER BY p_start)
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
                    -- subtract the previous sum to get the sum of this range
     , p_end::text
FROM   cte2
WHERE  range_end    -- only rows at the end of each range
ORDER  BY 1 DESC    -- biggest sum first
LIMIT  1;           -- only return the winner

sqlfiddle for Postgres 9.3
db<>fiddle here for Postgres 12

You need an index for this to be fast with big tables. The best option would be a GiST index on (event_type_id, period). Details:


Filter rows that match your conditions, then sort by the start of the time range (lower(period)) and calculate:

  1. A running sum of quantity (running_sum).
  2. The start of the next period: (lead(lower(period), 1, 'infinity')). Defaults to 'infinity' for the last row to include the last range.
  3. The latest end of any period so far max(upper(period)).

If 2. is later than 3. it's the end of a (sub-)range (range_end).

In the outer SELECT filter rows with range_end and subtract the previous total to get the sum for the range. ORDER BY that result and return the first (LIMIT 1) greatest sum_quantity. Voilá.


To select all of Jan 7th, 2016, the clean expression is:

'[2016-01-07 00:00:00+00,2016-01-08 00:00:00+00)'::tstzrange


'[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange


Since the default precision of timestamp values is 6 decimal digits (microseconds resolution), you could also use:

'[2016-01-07 00:00:00+00,2016-01-07 23:59:59.999999+00]'::tstzrange

But that's messy and depends on an implementation detail that might change (even if unlikely). It's not subject to rounding errors, since timestamps are stored as integer values in modern Postgres:

  • 1
    This doesn't correctly account for multiple overlaps. I'll use digits instead of dates for brevity: with ranges A: [1, 5], B: [2, 6], C: [3, 8], D: [7, 9], in the interval [3, 5] there are three overlapping ranges (A, B and C), only two in [5, 6] (B & C) and again for [7, 8] (C & D). Your query sums the values of A, B, C and D, because only for D is range_end true. In reality D should never be summed, it never overlaps with A and B. – Martijn Pieters May 19 '20 at 13:51
  • @Martin: In reality, D is excluded a priori by the predicate WHERE period && '[3,5]'::int4range and can never be summed. Ignoring the outer range, the definition of "overlapping" seems to be ambiguous. This answer understands it in a cascading manner, like tiles on a traditional roof: those are "overlapping" (rainproof), though the highest tile never overlaps with the lowest tile. – Erwin Brandstetter May 19 '20 at 14:32
  • Agreed, the definition of 'overlapping' is ambiguous. I was looking for the specific interval with which the largest number of rows overlap. I found my answer at How to get maximum number of concurrent events in postgresql?; a union of start and end times with +1 and -1 counters lets you aggregate to a measure of concurrency. – Martijn Pieters May 19 '20 at 14:38
  • @MartijnPieters: I clarified accordingly above to avoid future misunderstandings. Your task is subtly different in nature. At a closer look, your task is also subtly different from the question you linked to, as that checks for the point in time with the most overlapping ranges. Your problem is harder, looking for the range with the most overlapping ranges. – Erwin Brandstetter May 19 '20 at 15:03
  • My output requirements didn't actually include the interval end, only the start, so I was ambiguous as well. My apologies! – Martijn Pieters May 19 '20 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.