Tsrange - subtract differences between two tables based on id field
I have two tables. free_time
and appointment
. I would like to subtract all the recordings from the free time recordings. The result will be a set of records with free time left. It is guaranteed that the appointments will exist within free_time. I would like to make this request WHERE doctor_id = 1
.
They have the following entries:
CREATE TABLE free_time
AS
SELECT freetime::tsrange, doctor_id
FROM ( VALUES
('[2017-04-19 09:00, 2017-04-19 12:30)', 1),
('[2017-04-19 13:30, 2017-04-19 15:30)', 1),
('[2017-04-20 08:30, 2017-04-20 16:30)', 1),
('[2017-04-19 09:00, 2017-04-19 16:30)', 2)
) AS t(freetime, doctor_id);
CREATE TABLE appointment
AS
SELECT appointment::tsrange, doctor_id
FROM ( VALUES
('[2017-04-19 10:30, 2017-04-19 11:30)', 1),
('[2017-04-19 13:30, 2017-04-19 14:30)', 1),
('[2017-04-20 10:30, 2017-04-20 13:30)', 1),
('[2017-04-20 14:30, 2017-04-20 16:30)', 1),
('[2017-04-19 10:30, 2017-04-19 11:30)', 2)
) AS t(appointment, doctor_id);
The resulting set should look something like this:
["2017-04-19 09:00:00","2017-04-19 10:30:00"),
["2017-04-19 11:30:00","2017-04-19 12:30:00"),
["2017-04-19 14:30:00","2017-04-19 15:30:00"),
["2017-04-20 08:30:00","2017-04-20 10:30:00"),
["2017-04-20 13:30:00","2017-04-20 14:30:00"),
+3
source to share
1 answer
There are two functions (from my older answer with a minor fix):
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, 'tsrange'::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, 'tsrange'::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;
After that, your request might be:
with t as (
select ft.doctor_id, freetime, range_exclude(freetime, array_agg(appointment)) as ex
from free_time ft join appointment ap on (ft.doctor_id = ap.doctor_id)
group by ft.doctor_id, freetime)
select doctor_id, unnest(ex) from t order by 1,2;
Result:
╔═══════════╤══════════════════════════════════════ ══════════╗ ║ doctor_id │ unnest ║ ╠═══════════╪═════════════════════════════════════ ══════════╣ ║ 1 │ ["2017-04-19 09:00:00", "2017-04-19 10:30:00") ║ ║ 1 │ ["2017-04-19 11:30:00", "2017-04-19 12:30:00") ║ ║ 1 │ ["2017-04-19 14:30:00", "2017-04-19 15:30:00") ║ ║ 1 │ ["2017-04-20 08:30:00", "2017-04-20 10:30:00") ║ ║ 1 │ ["2017-04-20 13:30:00", "2017-04-20 14:30:00") ║ ║ 2 │ ["2017-04-19 09:00:00", "2017-04-19 10:30:00") ║ ║ 2 │ ["2017-04-19 11:30:00", "2017-04-19 16:30:00") ║ ╚═══════════╧═════════════════════════════════ ══════════╝
+1
source to share