Find numbers not included in any range
I understand why the difference operator for ranges fails if the result is two non-overlapping ranges. But I'm not sure what the workaround is.
A simple conceptual example would be if I kept a calendar and had an entry for each appointment, with a timestamp range field storing the time of the meeting; what would be an easy way to create a list of times a person was free on a specific day?
It looks like a simple, basic thing to do with ranges, but I can't think of a way to do it that is not unacceptably complex.
+1
source to share
2 answers
create or replace function range_exclude(anyelement, anyelement) returns anyarray as $$
declare
r1 text;
r2 text;
begin
-- Check input parameters
if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tstzrange'::regtype) then
raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
end if;
-- If result is single element
if ($1 &< $2 or $1 &> $2) then
return array[$1 - $2];
end if;
-- Else build array of two intervals
if lower_inc($1) then r1 := '['; else r1 := '('; end if;
r1 := r1 || lower($1) || ',' || lower($2);
if lower_inc($2) then r1 := r1 || ')'; else r1 := r1 || ']'; end if;
if upper_inc($2) then r2 := '('; else r2 := '['; end if;
r2 := r2 || upper($2) || ',' || upper($1);
if upper_inc($1) then r2 := r2 || ']'; else r2 := r2 || ')'; end if;
return array[r1, r2];
end $$ immutable language plpgsql;
create or replace function range_exclude(anyelement, anyarray) returns anyarray as $$
declare
i int;
j int;
begin
-- Check input parameters
if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tstzrange'::regtype) then
raise exception 'Function accepts only range types but got % type.', pg_typeof($1);
end if;
if array_length($2,1) is null then
return array[$1];
end if;
$0 := range_exclude($1,$2[array_lower($2,1)]);
for i in array_lower($2,1) + 1 .. array_upper($2,1) loop
select array(select x from (select unnest(range_exclude(x,$2[i])) from unnest($0) as t(x)) as t(x) where not isempty(x)) into $0;
end loop;
return $0;
end $$ immutable language plpgsql;
select range_exclude(numrange(8,17), array[numrange(10,11), numrange(13,20)]);
Tests:
select range_exclude(numrange(1,10), numrange(5,6));
select range_exclude(numrange(8,17), array[numrange(10,11), numrange(13,15)]);
Results:
{"[1,5)","[6,10)"}
{"[8,10)","[11,13)","[15,17)"}
And the same for timestamps:
select range_exclude(
tstzrange('2016-07-28 8:00','2016-07-28 17:00'),
array[
tstzrange('2016-07-28 10:00','2016-07-28 11:00'),
tstzrange('2016-07-28 13:00','2016-07-28 15:00')]);
Result:
{"[\"2016-07-28 08:00:00+03\",\"2016-07-28 10:00:00+03\")","[\"2016-07-28 11:00:00+03\",\"2016-07-28 13:00:00+03\")","[\"2016-07-28 15:00:00+03\",\"2016-07-28 17:00:00+03\")"}
+1
source to share
If the ranges don't overlap, use a window function, lead()
or lag()
:
create table plan (duration tsrange);
insert into plan values
('[2015-01-01 10:00:00, 2015-01-01 12:00:00)'),
('[2015-01-01 14:00:00, 2015-01-01 16:00:00)'),
('[2015-01-01 18:00:00, 2015-01-01 20:00:00)');
select tsrange(upper(duration), lower(lead(duration) over (order by duration))) free_time
from plan;
free_time
-----------------------------------------------
["2015-01-01 12:00:00","2015-01-01 14:00:00")
["2015-01-01 16:00:00","2015-01-01 18:00:00")
["2015-01-01 20:00:00",)
(3 rows)
+1
source to share