Consider my schema:
CREATE TABLE t_date (
t_date_id int PRIMARY KEY
, valid_from date NOT NULL
, valid_to date DEFAULT 'infinity'
);
Sometimes I have valid_to
dates, sometimes I have infinity
there ...
How do I filter properly to get the row with the shortest range?
I tried with:
(DATE_PART('day', valid_to::timestamp - valid_from::timestamp))
But that resulted in:
PG::DatetimeFieldOverflow: ERROR: cannot subtract infinite timestamps`
I have filters to select the valid range: valid_from <= ? AND valid_to > ?
The idea is to fetch the (one) valid row with the shortest range.
INSERT INTO t_date VALUES
(1, '2020-01-01', '2020-09-01')
, (2, '2020-01-10', '2020-01-12')
, (3, '2020-01-15', 'INFINITY')
, (4, '2020-01-16', 'INFINITY') -- shortest among infinities
, (5, '2020-01-14', 'INFINITY')
;
If today is 11/jan
I expect to get '2020-01-10' | '2020-01-12'
because it's valid for 11/jan and shortest.
If today is 14/jan
I expect to get '2020-01-01' | '2020-09-01'
because it's valid for 14/jan and shortest.
If today is 17/jan
I expect to get '2020-01-16' | 'INFINITY'
.
If later I created something like '2020-01-15' | '2059-01-15'
, that should be returned instead, because it's shorter than the INFINITY row.
SELECT *
FROM t_date
WHERE valid_from <= ?
AND valid_to > ?
ORDER BY NULLIF(valid_to, 'infinity') - valid_from
, valid_from DESC -- tiebreaker: later start first
-- , t_date_id -- optional additional tiebreaker
LIMIT 1;
db<>fiddle here
NULLIF()
converts 'infinity'
to NULL
, so the whole expression becomes NULL
, which sorts last in default ASCENDING
order. Infinite ranges should come last for your task.
To break ties among infinite ranges, additionally sort by valid_from DESC
- which is defined NOT NULL
, so we don't need special treatment for the NULL
case. As a (welcome?) side effect, this also breaks ties among other ranges of equal length, picking the latest start.
To get deterministic results, you may want to add another, unambiguous tiebreaker (like the PK column) if identical ranges are allowed.
Related: