I'm trying to combine multiple date ranges (my load is about max 500, most cases 10) that may or may not overlap into the largest possible contiguous date ranges. For example:



INSERT INTO test (range) VALUES 
  (DATERANGE('2015-01-01', '2015-01-05')),
  (DATERANGE('2015-01-01', '2015-01-03')),
  (DATERANGE('2015-01-03', '2015-01-06')),
  (DATERANGE('2015-01-07', '2015-01-09')),
  (DATERANGE('2015-01-08', '2015-01-09')),
  (DATERANGE('2015-01-12', NULL)),
  (DATERANGE('2015-01-10', '2015-01-12')),
  (DATERANGE('2015-01-10', '2015-01-12'));

Table looks like:

 id |          range
  1 | [2015-01-01,2015-01-05)
  2 | [2015-01-01,2015-01-03)
  3 | [2015-01-03,2015-01-06)
  4 | [2015-01-07,2015-01-09)
  5 | [2015-01-08,2015-01-09)
  6 | [2015-01-12,)
  7 | [2015-01-10,2015-01-12)
  8 | [2015-01-10,2015-01-12)
(8 rows)

Desired results:

 [2015-01-01, 2015-01-06)
 [2015-01-07, 2015-01-09)
 [2015-01-10, )

Visual representation:

1 | =====
2 | ===
3 |    ===
4 |        ==
5 |         =
6 |             =============>
7 |           ==
8 |           ==
  | ====== == ===============>

6 Answers 6


Assumptions / Clarifications

  1. No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.)
  1. Since date is a discrete type, all ranges have default [) bounds. The manual:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

For other types (like tsrange!) I would enforce the same if possible:

Solution with pure SQL

With CTEs for clarity:

   SELECT range
        , COALESCE(lower(range),'-infinity') AS startdate
        , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
   FROM   test
, b AS (
   SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
   FROM   a
, c AS (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM   b
SELECT daterange(min(startdate), max(enddate)) AS range
FROM   c

Or, the same with subqueries, faster but less easy too read:

SELECT daterange(min(startdate), max(enddate)) AS range
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM  (
      SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
      FROM  (
         SELECT range
              , COALESCE(lower(range),'-infinity') AS startdate
              , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
         FROM   test
         ) a
      ) b
   ) c


a: While ordering by range, compute the running maximum of the upper bound (enddate) with a window function.
Replace NULL bounds (unbounded) with +/- infinity just to simplify (no special NULL cases).

b: In the same sort order, if the previous enddate is earlier than startdate we have a gap and start a new range (step).
Remember, the upper bound is always excluded.

c: Form groups (grp) by counting steps with another window function.

In the outer SELECT build ranges from lower to upper bound in each group. Voilá.

Or with one less subquery level, but flipping sort order:

SELECT daterange(min(COALESCE(lower(range), '-infinity')), max(enddate)) AS range
   SELECT *, count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
   FROM  (
      SELECT range
           , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
           , lead(lower(range)) OVER (ORDER BY range) As nextstart
      FROM   test
      ) a
   ) b

Sort the window in the second step with ORDER BY range DESC NULLS LAST (with NULLS LAST) to get perfectly inverted sort order. This should be cheaper (easier to produce, matches sort order of suggested index perfectly) and accurate for corner cases with rank IS NULL. See:

Related answer with more explanation:

Procedural solution with plpgsql

Works for any table / column name, but only for type daterange.
Procedural solutions with loops are typically slower, but in this special case I expect the function to be substantially faster since it only needs a single sequential scan:

CREATE OR REPLACE FUNCTION f_range_agg(_tbl text, _col text)
  RETURNS SETOF daterange AS
   _lower     date;
   _upper     date;
   _enddate   date;
   _startdate date;
   FOR _lower, _upper IN EXECUTE
         SELECT COALESCE(lower(t.%2$I),'-infinity')  -- replace NULL with ...
              , COALESCE(upper(t.%2$I), 'infinity')  -- ... +/- infinity
         FROM   %1$I t
         ORDER  BY t.%2$I
         $sql$, _tbl, _col)
      IF _lower > _enddate THEN     -- return previous range
         RETURN NEXT daterange(_startdate, _enddate);
         SELECT _lower, _upper  INTO _startdate, _enddate;
      ELSIF _upper > _enddate THEN  -- expand range
         _enddate := _upper;
      -- do nothing if _upper <= _enddate (range already included) ...
      ELSIF _enddate IS NULL THEN   -- init 1st round
         SELECT _lower, _upper  INTO _startdate, _enddate;
      END IF;
   IF FOUND THEN                    -- return last row
      RETURN NEXT daterange(_startdate, _enddate);
   END IF;
$func$  LANGUAGE plpgsql;


SELECT * FROM f_range_agg('test', 'range');  -- table and column name

The logic is similar to the SQL solutions, but we can make do with a single pass.

SQL Fiddle.


The usual drill for handling user input in dynamic SQL:


For each of these solutions a plain (default) btree index on range would be instrumental for performance in big tables:

CREATE INDEX foo on test (range);

A btree index is of limited use for range types, but we can get pre-sorted data and maybe even an index-only scan.

  • @ErwinBrandstetter Hey, I am trying to understand this query (the one with CTEs), but I can't figure out what (CTE A) max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate is for? Can't it be just COALESCE(upper(range), 'infinity') as enddate? AFAIK max() + over (order by range) will return just upper(range) here.
    – user606521
    Commented Jul 27, 2018 at 8:29
  • 2
    @user606521: What you observe is the case if the upper bound grows continuously when sorted by range - which may be guaranteed for some data distributions and then you can simplify as you suggest. Example: fixed length ranges. But for ranges of arbitrary length the next range can have a greater lower bound, but still a lower upper bound. So we need the greatest upper bound of all ranges so far. Commented Jul 28, 2018 at 0:23
  • How does github.com/pjungwir/range_agg compare to this solution?
    – No_name
    Commented Jun 6, 2020 at 5:28
  • @No_name: Wasn't aware of it; was created years after my answer; have not tested it. From a quick look, it provides actual aggregate functions for any range type, while my f_range_agg() is a table function for daterange only. (Might be extended to work with anyrange.) You can try it and tell us how performance compares ... Commented Jun 6, 2020 at 12:53

I've come up with this:

DO $$                                                                             
    i date;
    a daterange := 'empty';
    day_as_range daterange;
    extreme_value date := '2100-12-31';
    FOR i IN 
                 COALESCE(upper(range) - interval '1 day', extreme_value), 
                 interval '1 day'
        FROM rangetest 
        ORDER BY 1
        day_as_range := daterange(i, i, '[]');
            IF isempty(a)
            THEN a := day_as_range;
            ELSE a = a + day_as_range;
            END IF;
        EXCEPTION WHEN data_exception THEN
            RAISE INFO '%', a;
            a = day_as_range;

    IF upper(a) = extreme_value + interval '1 day'
    THEN a := daterange(lower(a), NULL);
    END IF;

    RAISE INFO '%', a;

Still needs a bit of honing, but the idea is the following:

  1. explode the ranges to individual dates
  2. doing this, replace the infinite upper bound with some extreme value
  3. based on the ordering from (1), start building the ranges
  4. when the union (+) fails, return the already built range and reinitialize
  5. finally, return the rest - if the predefined extreme value is reached, replace it with NULL to get an infinite upper bound
  • It strikes me as rather expensive to run generate_series() for every row, especially if there can be open ranges ... Commented May 9, 2015 at 22:56
  • @ErwinBrandstetter yes, that's an issue I wanted to test (after my first extreme was 9999-12-31 :). At the same time, I am wondering why my answer has more upvotes than yours. This is possibly easier to understand... So, future voters: Erwin's answer is superior to mine! Vote there! Commented May 11, 2015 at 8:19

Some years ago I tested different solutions (amongst others some similar to those from @ErwinBrandstetter) for merging overlapping periods on a Teradata system and I found the following the most efficient one (using Analytical Functions, newer version of Teradata have built-in functions for that task).

  1. sort the rows by start date
  2. find the maximum end date of all previous rows: maxEnddate
  3. if this date is less than the current start date, you found a gap. Only keep those rows plus the first row within the PARTITION (which is indicated by a NULL) and filter all other rows. Now you get the start date for each range and the end date of the previous range.
  4. Then you simply get the next row's maxEnddate using LEAD and you're almost done. Only for the last row LEAD returns a NULL, to solve this calculate the maximum end date of all rows of a partition in step 2 and COALESCE it.

Why it was faster? Depending on the actual data step #2 might greatly reduce the number of rows, so the next step needs to operate on a small subset only, additionally it removes aggregation.


            ,COALESCE(LEAD(maxPrevEnddate) -- next row's end date
                      OVER (ORDER BY startdate) 
                     ,maxEnddate)          -- or maximum end date
            ) AS range

     ,COALESCE(LOWER(range),'-infinity') AS startdate

   -- find the maximum end date of all previous rows
   -- i.e. the END of the previous range
     ,MAX(COALESCE(UPPER(range), 'infinity'))
      OVER (ORDER BY range

   -- maximum end date of this partition
   -- only needed for the last range
     ,MAX(COALESCE(UPPER(range), 'infinity'))
      OVER () AS maxEnddate
   FROM test
 ) AS dt
WHERE maxPrevEnddate < startdate -- keep the rows where a range start
   OR maxPrevEnddate IS NULL     -- and keep the first row

As this was fastest on Teradata, I don't know if it's the same for PostgreSQL, would be nice to get some actual performance numbers.

  • Is it sufficient to order by only by start of range? Does it work if you have three ranges each with same start but varying end?
    – Salman A
    Commented Jan 24, 2019 at 14:30
  • 1
    It works with the start date only, no need to add the end date sorted descending (you only check for the a gap, so whatever is the first row for a given date will match)
    – dnoeth
    Commented Jan 24, 2019 at 17:47

In PostgreSQL 14, a feature called Multiranges was introduced, which is particularly suited for scenarios like this where we want to aggregate overlapping date ranges. Here's how we could utilize this feature:

    range_agg(multirange(range))) FROM test;

Here's a step-by-step breakdown of what's happening in the query:

  1. multirange(range): This function creates a multirange containing a single range from each row of the test table.
  2. range_agg(...): This function aggregates all multiranges produced in the previous step into a single multirange that consists of non-overlapping ranges. If there are any overlapping ranges in the original data, this function will merge them into a single larger range.
  3. UNNEST(...): Finally, this function converts the multirange into individual ranges. The result is a table with each row representing a separate non-overlapping date range.

Do remember to ensure that you're running PostgreSQL 14 or later, as this feature isn't available in earlier versions.


For fun, I gave it a shot. I found this to be the fastest and cleanest method to do this. First we define a function that merges if there is an overlap or if the two inputs are adjacent, if there is no overlap or adjacency we simply return the first daterange. Hint + is a range union in the context of ranges.

CREATE FUNCTION merge_if_adjacent_or_overlaps (d1 daterange, d2 daterange)
RETURNS daterange AS $$
    CASE WHEN d1 && d2 OR d1 -|- d2
    THEN d1 + d2
    ELSE d1

Then we use it like this,

SELECT DISTINCT ON (lower(cumrange)) cumrange
  SELECT merge_if_adjacent_or_overlaps(
    lag(t1.range) OVER (ORDER BY t1.range)
  ) AS cumrange
  FROM test AS t1
) AS t
ORDER BY lower(cumrange)::date, upper(cumrange)::date DESC NULLS first;
  • 2
    The window function only considers two adjacent values at a time and misses chains. Try with ('2015-01-01', '2015-01-03'), ('2015-01-03', '2015-01-05'), ('2015-01-05', '2015-01-06'). Commented Dec 28, 2017 at 2:12

Just assign value 1 to each row where the start is greater than the previous end (ie. there is a gap in dates) then you can sum the values in order to give each range a group id and get min and max of each. simple!

with date_ranges as
   select 'one' as partition_columns, cast('2023-01-01' as date) as date_start, cast('2023-01-30' as date) as date_end
   union all
   select 'one' as partition_columns, cast('2023-01-15' as date) as date_start, cast('2023-02-20' as date) as date_end
   union all
   select 'two' as partition_columns, cast('2023-02-15' as date) as date_start, cast('2023-02-28' as date) as date_end
   union all
   select 'two' as partition_columns, cast('2023-03-01' as date) as date_start, cast('2023-03-10' as date) as date_end
   union all
   select 'two' as partition_columns, cast('2023-03-10' as date) as date_start, cast('2023-03-25' as date) as date_end

select partition_columns, min(date_start) as date_start, max(date_end) as date_end
from (
    select partition_columns, date_start, date_end, sum(start_flag) over (partition by partition_columns order by date_start) range_group
    from (
        select partition_columns, date_start, date_end,
               case when lag(date_end,1) over (partition by partition_columns order by date_start) >= date_start then 0 else 1 end as start_flag
        from date_ranges
    ) c
) g
group by partition_columns, range_group
order by 1, 2, 3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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