在PostgreSQL中的两个日期之间获取结果 [英] Getting results between two dates in PostgreSQL
问题描述
我有下表:
+-----------+-----------+------------+----------+
| id | user_id | start_date | end_date |
| (integer) | (integer) | (date) | (date) |
+-----------+-----------+------------+----------+
字段 start_date
和 end_date
持有日期值,如 YYYY-MM-DD
。
Fields start_date
and end_date
are holding date values like YYYY-MM-DD
.
此表中的条目可能如下所示:(1,120,2012-04-09,2012-04-13)
。
An entry from this table can look like this: (1, 120, 2012-04-09, 2012-04-13)
.
我必须编写一个查询,可以获取匹配一定期限的所有结果。
I have to write a query that can fetch all the results matching a certain period.
问题是如果我想从 2012-01-01
中获取结果到 2012-04-12
,即使有 start_date =2012-04-09
和 end_date =2012-04-13
。
The problem is that if I want to fetch results from 2012-01-01
to 2012-04-12
, I get 0 results even though there is an entry with start_date = "2012-04-09"
and end_date = "2012-04-13"
.
推荐答案
SELECT *
FROM mytable
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);
日期时间函数是文档中的相关部分。
Datetime functions is the relevant section in the docs.
这篇关于在PostgreSQL中的两个日期之间获取结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!