考虑我的模式：

CREATE TABLE t_date (
  t_date_id  int PRIMARY KEY
, valid_from date NOT NULL
, valid_to   date DEFAULT 'infinity'
);

有时我会 valid_to 约会，有时我有 infinity 在那里。。。
如何正确过滤以获得范围最短的行？
我试过：

(DATE_PART('day', valid_to::timestamp - valid_from::timestamp))

但结果是：

PG::DatetimeFieldOverflow: ERROR:  cannot subtract infinite timestamps`

我有筛选器来选择有效范围： valid_from <= ? AND valid_to > ? 其思想是以最短的范围获取（一）个有效行。

示例

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')
;

如果今天是 11/jan 我希望得到 '2020-01-10' | '2020-01-12' 因为有效期是1月11日，最短的。
如果今天是 14/jan 我希望得到 '2020-01-01' | '2020-09-01' 因为它的有效期是1月14日和最短的。
如果今天是 17/jan 我希望得到 '2020-01-16' | 'INFINITY' .
如果后来我创造了 '2020-01-15' | '2059-01-15' ，应该返回，因为它比无限行短。

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;

1条答案

按热度按时间

qacovj5a1#

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<>在这里摆弄 NULLIF() 皈依者 'infinity' 至 NULL ，所以整个表达式变成 NULL ，默认情况下最后排序 ASCENDING 命令。无限范围应该排在你任务的最后。
要打破无限范围之间的联系，请另外按 valid_from DESC -定义了什么 NOT NULL 所以我们不需要特殊的治疗 NULL 案例。作为一个（欢迎？）副作用，这也打破了其他长度相等的范围之间的联系，选择了最新的开始。
为了得到确定的结果，如果允许相同的范围，您可能需要添加另一个明确的tiebreaker（如pk列）。
相关：
将空值排序到表的末尾

赞(0）回复(0）举报 2021-07-24

sql—处理“无限”时获取日期范围最短的行

示例

1条答案

相关问题

热门标签

最新问答