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

postgresql documentation gives an example timerange definition:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
  subtype = time,
  subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

The problem is that when time crosses the day barrier, the range can no longer be used, e.g.

SELECT '[22:00, 06:00]'::timerange;

will produce an error:

ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: SELECT '[22:00, 06:00]'::timerange;

I need to be able to check if the current time is between 22:00 today and 06:00 tomorrow.

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.

StackExchange Database Administrators Q#226063, answer score: 3