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.
Best Answer
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.One could define a custom rangetype for this purpose.
Solution 2:
Alternatively, you can have two columns:
and write the check as:
Both solutions were suggested by the members of Freenode #postgresql community.