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

Question

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.

Answer 1

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.

Answer 2

A range require a lower-bound and an upper-bound, time data type allows values:

+------------------------------------+--------------+-----------------------------------+---------------+---------------+---------------------------+
| Name                               | Storage Size | Description                       | Low Value     | High Value    | Resolution                |
+------------------------------------+--------------+-----------------------------------+---------------+---------------+---------------------------+
| time [ (p) ] [ without time zone ] | 8 bytes      | time of day (no date)             | 00:00:00      | 24:00:00      | 1 microsecond / 14 digits |
+------------------------------------+--------------+-----------------------------------+---------------+---------------+---------------------------+
| time [ (p) ] with time zone        | 12 bytes     | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 | 1 microsecond / 14 digits |
+------------------------------------+--------------+-----------------------------------+---------------+---------------+---------------------------+

In fact in your example: 22:00 till 06:00. Is it 06:00 of next day or two days after tomorrow?

If you need to cross this boundaries, you should use another data type:

• tsrange : Range of timestamp without time zone
• tstzrange : Range of timestamp with time zone
• daterange : Range of date

Answer 3

use two columns,

select (x <= start_time) <> (x <= end_time) <> (start_time < end_time) as x_between