In one of my apps, users have a choice to set time interval to tell the app to play a sound for push notifications. The choice is stored in a PostgreSQL table columns _from time with time zone and _to time with time zone. PostgreSQL has OVERLAPS operator that makes it easy to do SQL query. Here, (_from, _to) OVERLAPS (current_time, current_time) returns true if _from <= current_time < _to.

The Problem

select ('07:20:00+00'::time,'08:30:00+00'::time) OVERLAPS (current_time, current_time) returns TRUE when current_time is between 07:20 and 08:30. Perfect.

What if a user wants to have sound played between 08.30AM to next day 07:30AM. The SQL query will be like select ('08:30:00+00'::time,'07:30:00+00'::time) OVERLAPS (current_time, current_time). This fails.

# select ('08:30:00+00'::time,'07:30:00+00'::time) OVERLAPS ('09:30:00+00'::time,'09:30:00+00'::time);
(1 row)

The above query doesn't work as expected, probably _from is greater than _to.


Use overlap operator only when _from < _to. So the sql is something like
(case when _from < _to then ((_from, _to) OVERLAPS (current_time, current_time)) else (case when _from <= current_time OR _to >= current_time then true else false end) end)