PostgreSQL: How to Roll Join between two dates on both sides?

I have some shift and break data I would like to join together. Below is what it looks like:

enter image description here

You can see I have one set of shifts and one set of breaks. I want to join the two tables on worker where the break start and end time fall between the start and end shift. So the final result will look like this: enter image description here

You can see that in this inner join, one of the breaks which does not fit the condition is excluded from the result. Pardon that these are pictures rather than code, as I don't have the replicability capability at this moment.

I am aware of the usual:

SELECT 
*
FROM
shifts inner JOIN breaks ON
BREAKS.worker = shifts.worker AND
breaks.break_start between shifts.shift_start AND shifts.end_shift;

But I am not sure how to rope in the break_end column into this equation. Thank you.

1 answer

  • answered 2018-11-08 08:13 Laurenz Albe

    The range data types and their “contains” operator should solve this problem quite nicely:

    tstzrange(shifts.shift_start, shifts.end_shift)
       @> tstzrange(breaks.break_start, breaks.break_end)