How to check if the current time is between two times?
Solution 1:

Define the range column as 'hours since midnight'. Continuing with the 22:00 and 06:00 range example, the range value would be [22, 30] (22 hours since midnight, 1 day + 6 hours since midnight), i.e.

current_date::timestamptz + interval '22 hours' AND
current_date::timestamptz + interval '30 hours'


One could define a custom rangetype for this purpose.

Solution 2:

Alternatively, you can have two columns:

maintenance_window_starts_at time without time zone
maintenance_window_duration interval


and write the check as:

SELECT
  maintenance_window_starts_at IS NULL OR
  now() BETWEEN
    current_date + maintenance_window_starts_at AND
    current_date + maintenance_window_starts_at + maintenance_window_duration
FROM maintenance_task;


Both solutions were suggested by the members of Freenode #postgresql community.