I have two tables free_time and appointment. Both contain tsranges.
How do I write a query (or function) that determines the actual free time after "subtracting" the difference the appointment from the freetime?
INSERT INTO free_time(freetime)
VALUES('[2017-04-19 09:00, 2017-04-19 12:30)');
INSERT INTO appointment(appointment)
VALUES('[2017-04-19 10:30, 2017-04-19 11:30)');
I want the result to be something like:
["2017-04-19 9:00","2017-04-19 10:30:00"),
["2017-04-19 11:30:00","2017-04-19 12:30:00")
You'll have to break apart the range, from the docs
The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be represented.
In order to do this you can use lower, and upper
SELECT tsrange( lower(freetime), lower(appointment) ) AS before_appointment,
tsrange( upper(appointment), upper(freetime) ) AS after_appointment
FROM ( VALUES
(
'[2017-04-19 09:00, 2017-04-19 12:30)'::tsrange,
'[2017-04-19 10:30, 2017-04-19 11:30)'::tsrange
)
) AS t(freetime,appointment)
WHERE freetime @> appointment;
before_appointment | after_appointment
-----------------------------------------------+-----------------------------------------------
["2017-04-19 09:00:00","2017-04-19 10:30:00") | ["2017-04-19 11:30:00","2017-04-19 12:30:00")
(1 row)