CODEWITHSUNDEEP
postgresqltimestamp
Tito
Tito

Reputation: 671

How to compare timestamps between 2 time points that are in conflict

I have an appointments table. I need to check that no date between validfrom and validuntil conflict with another record that has a date that might be partially or fully in between the date of another record.

E.g. the picture below, record 2's date 08.02.2019 - 05.02.2019 is in conflict with record 3's date 03.02.2019 - 09.02.2019, because day 08.02.2019 and 09.02.2017 are in the range of the 3rd record.

enter image description here

How would I be able to produce a table of results that shows me any appointments that are in conflict for each staff member?

In other words I want this:

enter image description here

Here is the basic query gist:

select sa.staffid,sa.appointmentid,a.validfrom,a.validuntil 
from staffappointment sa
join staff s on s.id = sa.staffid
join appointment a on a.id = sa.appointmentid
where .....

Upvotes: 0

Views: 39

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

You'd have to use a self join and check for overlaps:

SELECT sa1.staffid, sa1.appointmentid, a1.validfrom, a1.validuntil
FROM staffappointment AS sa1
   JOIN appointment a1 ON a1.id = sa1.appointmentid
   JOIN staffappointment AS sa2
      ON sa1.staffid = sa2.staffid AND sa1.appointmentid <> sa2.appointmentid
   JOIN appointment a2 ON a2.id = sa2.appointmentid
WHERE tstzrange(a1.validfrom, a1.validuntil) && tstzrange(a2.validfrom, a2.validuntil);

Upvotes: 2

Related Questions