在PostgreSQL中获取两个日期之间的结果

Question

我有以下表格：

+-----------+-----------+------------+----------+
| id        | user_id   | start_date | end_date |
| (integer) | (integer) | (date)     | (date)   |
+-----------+-----------+------------+----------+

字段start_date和end_date持有日期值（例如YYYY-MM-DD）。

该表中的一条记录可能长这样：(1, 120, 2012-04-09, 2012-04-13)。

我必须编写一个查询，可以获取与某个时间段匹配的所有结果。

问题在于，如果我想从2012-01-01到2012-04-12获取结果，尽管存在start_date ="2012-04-09"和end_date = "2012-04-13"的条目，但我却得到了0个结果。

- Psyche

---

@MarcoMariani 我希望它能像示例中那样正常工作。 - Psyche

这个问题并没有明确说明“匹配”是什么意思。是包含？被包含？重叠？还是完全匹配？ - kgrittn

Answer 1

 SELECT *
   FROM mytable
  WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);

Datetime functions 是文档中相关的部分。

- Marco Mariani

---

这不是我的直接问题，但使用OVERLAPS解决了我的问题。非常感谢 :) - SRC

Answer 2

假设您想要所有“重叠”时间段，即所有至少有一天共同的时间段。

试着在时间轴上想象时间段并在眼前移动它们，您就会看到必要的条件。

SELECT *
FROM   tbl
WHERE  start_date <= '2012-04-12'::date
AND    end_date   >= '2012-01-01'::date;

对我来说，这比使用 OVERLAPS（另一种好方法，就像@Marco已经提供的）有时更快。

请注意微妙的区别。手册：

OVERLAPS自动将一对中较早的值作为起始值。每个时间段都被认为代表半开区间start <= time < end ，除非起始和结束相等，在这种情况下它表示单个时间瞬间。这意味着例如仅在端点上共享两个时间段不重叠。

加粗是我自己加的。

性能

对于大型表格，正确的索引会帮助提高性能（很多）。

CREATE INDEX tbl_date_inverse_idx ON tbl(start_date, end_date DESC);

如果您有其他选择性条件，可能需要使用另一个（前导）索引列。

请注意两个列的反向顺序。请参见：

• 优化时间戳范围内的查询（两个列）

- Erwin Brandstetter

Answer 3

刚有同样的问题，我这样回答了，如果能帮到你就好了。

select * 
from table
where start_date between '2012-01-01' and '2012-04-13'
or    end_date   between '2012-01-01' and '2012-04-13'

- Chris

Answer 4

要在任何语言环境下使查询正常工作，请考虑自己格式化日期。

SELECT * 
  FROM testbed 
 WHERE start_date >= to_date('2012-01-01','YYYY-MM-DD')
   AND end_date <= to_date('2012-04-13','YYYY-MM-DD');

- vyegorov

Answer 5

SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');

- user9251288

Answer 6

看到它无法工作的日期 - 那些日期小于或等于12的日期 - 我想知道它是否将日期解析为YYYY-DD-MM格式？

- Edmund

---

为什么不呢？这种格式取决于客户端的区域设置，数据库将其存储在几个字节中，没有任何格式。 - Szymon Lipiński

Answer 7

让我们尝试使用range数据类型。

--样本数据。

begin;
create temp table tbl(id  serial, user_id integer, start_date date, end_date date);
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-13');
insert into tbl(user_id, start_date, end_date) values(1, '2012-01-09', '2012-04-12');
insert into tbl(user_id, start_date, end_date) values(1, '2012-02-09', '2012-04-10');
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-10');
commit;

添加一个新的日期范围列。

begin;
alter table tbl add column tbl_period daterange ;
update tbl set tbl_period = daterange(start_date,end_date);
commit;

--现在是测试时间。

select * from tbl
    where tbl_period && daterange('2012-04-10' ::date, '2012-04-12'::date);

返回：

 id | user_id | start_date |  end_date  |       tbl_period
----+---------+------------+------------+-------------------------
  1 |       1 | 2012-04-09 | 2012-04-13 | [2012-04-09,2012-04-13)
  2 |       1 | 2012-01-09 | 2012-04-12 | [2012-01-09,2012-04-12)

更多参考：https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE

- jian

Answer 8

不冒犯，但为了检查 SQL 的性能，我执行了上述某些解决方案的 pgsql。

让我分享一下我遇到的前三种解决方案的统计数据。

1）花费时间：1.58 毫秒平均值

2）花费时间：2.87 毫秒平均值

3）花费时间：3.95 毫秒平均值

现在试试这个：

 SELECT * FROM table WHERE DATE_TRUNC('day', date ) >= Start Date AND DATE_TRUNC('day', date ) <= End Date

现在这个解决方案花了：1.61平均。

而最好的解决方案是由marco-mariani建议的第一个。

- rxpande

Answer 9

你必须使用日期部分提取方法：

SELECT * FROM testbed WHERE start_date  ::date >= to_date('2012-09-08' ,'YYYY-MM-DD') and date::date <= to_date('2012-10-09' ,'YYYY-MM-DD')

- boopathiraja