SQL 在 PostgreSQL 中获取两个日期之间的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10170544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting results between two dates in PostgreSQL
提问by Psyche
I have the following table:
我有下表：
+-----------+-----------+------------+----------+
| id | user_id | start_date | end_date |
| (integer) | (integer) | (date) | (date) |
+-----------+-----------+------------+----------+
Fields start_dateand end_dateare holding date values like YYYY-MM-DD.
字段start_date和end_date保存日期值，如YYYY-MM-DD.
An entry from this table can look like this: (1, 120, 2012-04-09, 2012-04-13).
从这个表中的条目可能是这样的：(1, 120, 2012-04-09, 2012-04-13)。
I have to write a query that can fetch all the results matching a certain period.
我必须编写一个查询来获取匹配某个时期的所有结果。
The problem is that if I want to fetch results from 2012-01-01to 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".
问题是，如果我想从2012-01-01to2012-04-12获取结果，即使有一个带有start_date = "2012-04-09"and的条目，我也会得到 0 个结果end_date = "2012-04-13"。
回答by Marco Mariani
SELECT *
FROM mytable
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);
Datetime functionsis the relevant section in the docs.
日期时间函数是文档中的相关部分。
回答by Erwin Brandstetter
Assuming you want all "overlapping" time periods, i.e. all that have at least one day in common.
假设您想要所有“重叠”的时间段，即所有至少有一天共同的时间段。
Try to envision time periods on a straight time line and move them around before your eyes and you will seethe necessary conditions.
试着在一条直线上想象时间段，在你眼前移动它们，你会看到必要的条件。
SELECT *
FROM tbl
WHERE start_date <= '2012-04-12'::date
AND end_date >= '2012-01-01'::date;
This is sometimes faster for me than OVERLAPS- which is the other good way to do it (as @Marco already provided).
这对我来说有时比OVERLAPS- 这是另一种好方法（正如@Marco 已经提供的那样）。
Note the subtle difference(per documentation):
请注意细微差别（根据文档）：
OVERLAPSautomatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open intervalstart <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
OVERLAPS自动以该对的较早值作为开始。每个时间段都被视为表示半开区间start <= time < end，除非开始和结束相等，在这种情况下它表示单个时间瞬间。这意味着例如只有一个共同端点的两个时间段不重叠。
Bold emphasis mine.
大胆强调我的。
Performance
表现
For big tables the right indexcan help performance (a lot).
对于大表，正确的索引可以帮助提高性能（很多）。
CREATE INDEX tbl_date_inverse_idx ON tbl(start_date, end_date DESC);
Possibly with another (leading) index column if you have additional selective conditions.
如果您有额外的选择性条件，可能与另一个（前导）索引列一起使用。
Note the inverse order of the two columns. Detailed explanation:
请注意两列的相反顺序。详细解释：
回答by Chris
just had the same question, and answered this way, if this could help.
刚刚有同样的问题，并以这种方式回答，如果这有帮助的话。
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'
回答by vyegorov
To have a query working in any locale settings, consider formatting the dateyourself:
要在任何区域设置中运行查询，请考虑自己格式化日期：
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');
回答by rxpande
No offense but to check for performance of sql I executed some of the above mentioned solutiona pgsql.
无意冒犯，只是为了检查 sql 的性能，我执行了上面提到的一些解决方案 pgsql。
Let me share you Statistics of top 3 solution approaches that I come across.
让我与您分享我遇到的前 3 种解决方案的统计数据。
1) Took : 1.58 MS Avg
1) 花费：平均 1.58 毫秒
2) Took : 2.87 MS Avg
2) 花费：平均 2.87 毫秒
3) Took : 3.95 MS Avg
3) 花费：平均 3.95 毫秒
Now try this :
现在试试这个：
SELECT * FROM table WHERE DATE_TRUNC('day', date ) >= Start Date AND DATE_TRUNC('day', date ) <= End Date
Now this solution took : 1.61 Avg.
现在这个解决方案花了：1.61 Avg。
And best solution is 1st that suggested by marco-mariani
最好的解决方案是marco-mariani建议的第一个
回答by user9251288
SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');
回答by boopathiraja
You have to use the date part fetching method:
您必须使用日期部分获取方法：
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')
回答by Edmund
Looking at the dates for which it doesn't work -- those where the day is less than or equal to 12 -- I'm wondering whether it's parsing the dates as being in YYYY-DD-MM format?
查看它不起作用的日期——那些日子小于或等于 12 的日期——我想知道它是否将日期解析为 YYYY-DD-MM 格式？
