3

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.

3 Answers 3

3

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.

1
  • beware that solution 2 still requires handling cross-day intervals; (23:00, 01:00) and current datetime 2018-12-31 00:30 would be transformed to 2018-12-31 00:30 BETWEEN 2018-12-31 23:00 AND 2019-01-01 01:00
    – theuses
    Aug 22, 2022 at 15:51
1

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
1

use two columns,

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.