时间戳

首页 > 解决方案 > tsrange计算时间间隔？

sql - tsrange计算时间间隔？

问题描述

我有一张预留房间的桌子：

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 09:30, 2010-01-01 10:30)');

问题：

我需要获得这个房间的剩余免费时间间隔：白天（09:00 - 18:00）的 30 分钟、45 分钟、1 小时和 1.30 分钟。怎么做？谢谢大家。

标签： sqlpostgresqldate-range

解决方案

以下查询将返回在 中有条目的所有日期的所有空闲时间间隔reservation：

WITH mins(lt) AS (
   /* get all lower bounds of intervals */
   SELECT lower(during)
   FROM reservation
   UNION
   /* get 18:00 for each day */
   SELECT date_trunc('day', lower(during)) + INTERVAL '18 hours'
   FROM reservation
), maxs(ut) AS (
   /* get all upper bounds of intervals */
   SELECT upper(during)
   FROM reservation
   UNION
   /* get 09:00 for each day */
   SELECT date_trunc('day', lower(during)) + INTERVAL '9 hours'
   FROM reservation
)
SELECT tsrange(ut, lt)  /* candidate for a free interval */
FROM mins
   JOIN maxs
      ON date_trunc('day', lt) = date_trunc('day', ut)
         AND ut < lt
/* exclude all such intervals that overlap an entry */
WHERE NOT EXISTS (SELECT 1 FROM reservation
                  WHERE during && tsrange(ut, lt, '()'))
ORDER BY ut;

按长度过滤它们留给读者作为练习。

推荐阅读