PostgreSQL OVERLAPS overator not inclusive of end time - Rails

Question

I am using Rails 5 and postgresql in my application.

The query is as follows.

.where('(start_time, end_time) OVERLAPS (time without time zone ?, time without time zone ?)',
               start_time, end_time)

Scenario 1:

• Range 1 => 0:00 - 08:00

  Range 2 => 07:59 - 12:00

This shows an overlap which is correct.

Scenario 2:

• Range 1 => 0:00 - 08:00

  Range 2 => 08:00 - 12:00

This does not show an overlap. This is incorrect since 08:00 falls in both time ranges.

I assume the issue is that OVERLAPS considers < end_time and not <= end_time.

Any idea on how to fix this?

Answer 1

The documentation makes this quite clear:

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

You can get what you want with range types and the overlaps operator &&:

WHERE tsrange(start_time, end_time, '[]') && tsrange(?, ?, '[]')

Here the third argument to tsrange specifies than both ends are included.

Using ranges has the additional advantage that you can support the condition with a GiST index.