时间戳

首页 > 解决方案 > 不确定如何在时间段之间获得空置单位

postgresql - 不确定如何在时间段之间获得空置单位

问题描述

不知道如何设置条件（或者是否需要操作；对于 SQL 来说是新鲜的）来查找空单元。

单位附有租赁的开始和结束日期。

INSERT INTO lease (unit, s_date, e_date) VALUES (1, '20200101','20200530');
INSERT INTO lease (unit, s_date, e_date) VALUES (2, '20200701','20201231');
INSERT INTO lease (unit, s_date, e_date) VALUES (3, '20200201','20210501');
INSERT INTO lease (unit, s_date, e_date) VALUES (4, '20200615','20201225');

即在 2020/06/01-30 之间，单元#1 和 2 是空置的并且应该显示。

然而，

SELECT * FROM lease WHERE (s_date NOT BETWEEN '20200601' AND '20200630') AND (e_date NOT BETWEEN  '20200601' AND '20200630');

结果也显示了第 3 单元

提前致谢！

标签： postgresql

解决方案

您的表定义不好：

  • 切勿使用日期以外的数据类型date。字符串和数字都不合适。

  • 用于daterange存储日期间隔。

所以这应该是：

CREATE TABLE lease (
   unit bigint NOT NULL,
   leased daterange NOT NULL
);

INSERT INTO lease VALUES (1, '[2020-01-01,2020-05-30]');
...

那么您的查询将是：

SELECT unit
FROM lease
GROUP BY unit
HAVING NOT range_agg(leased) && '[2020-06-01,2020-06-30]';

这需要 PostgreSQL v14 的多范围支持。&&是“重叠”运算符。

使用 PostgreSQL v13 及以下版本，您可以使用：

   SELECT unit
   FROM lease
EXCEPT
   SELECT unit
   FROM lease
   WHERE leased && '[2020-06-01,2020-06-30]';

请注意，即使您不在daterange表中使用，也可以使用此解决方案，因为您可以daterange在查询中使用该函数：

SELECT daterange('2020-01-01', '2020-05-30', '[]');

        daterange        
═════════════════════════
 [2020-01-01,2020-05-31)
(1 row)

推荐阅读