SQL 日期范围之间的 Postgresql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23335970/
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
Postgresql query between date ranges
提问by John
I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year.
我正在尝试查询我的 postgresql 数据库以返回日期在特定月份和年份的结果。换句话说，我想要一个月一年的所有值。
The only way i've been able to do it so far is like this:
到目前为止，我能够做到的唯一方法是这样的：
SELECT user_id
FROM user_logs
WHERE login_date BETWEEN '2014-02-01' AND '2014-02-28'
Problem with this is that I have to calculate the first date and last date before querying the table. Is there a simpler way to do this?
问题是我必须在查询表之前计算第一个日期和最后一个日期。有没有更简单的方法来做到这一点？
Thanks
谢谢
回答by MatBailie
With dates (and times) many things become simpler if you use >= start AND < end.
如果使用日期（和时间），许多事情会变得更简单>= start AND < end。
For example:
例如：
SELECT
user_id
FROM
user_logs
WHERE
login_date >= '2014-02-01'
AND login_date < '2014-03-01'
In this case you still need to calculate the start date of the month you need, but that should be straight forward in any number of ways.
在这种情况下，您仍然需要计算所需月份的开始日期，但这应该以多种方式直接进行。
The end date is also simplified; just add exactly one month. No messing about with 28th, 30th, 31st, etc.
结束日期也简化了；正好加一个月。不要搞乱 28 日、30 日、31 日等。
This structure also has the advantage of being able to maintain use of indexes.
这种结构还具有能够维护索引使用的优点。
Many people may suggest a form such as the following, but they do notuse indexes:
许多人可能会建议使用如下形式，但他们不使用索引：
WHERE
DATEPART('year', login_date) = 2014
AND DATEPART('month', login_date) = 2
This involves calculating the conditions for every single row in the table (a scan) and not using index to find the range of rows that will match (a range-seek).
这涉及计算表中每一行的条件（扫描），而不是使用索引来查找将匹配的行范围（范围搜索）。
回答by Ana María Martínez Gómez
From PostreSQL 9.2 Range Typesare supported. So you can write this like:
从 PostreSQL 9.2 开始支持范围类型。所以你可以这样写：
SELECT user_id
FROM user_logs
WHERE '[2014-02-01, 2014-03-01]'::daterange @> login_date
this should be more efficient than the string comparison
这应该比字符串比较更有效
回答by HayrolR
Just in case somebody land here... since 8.1 you can simply use:
以防万一有人在这里登陆……从 8.1 开始，您可以简单地使用：
SELECT user_id
FROM user_logs
WHERE login_date BETWEEN SYMMETRIC '2014-02-01' AND '2014-02-28'
From the docs:
从文档：
BETWEEN SYMMETRIC is the same as BETWEEN except there is no requirement that the argument to the left of AND be less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied.
BETWEEN SYMMETRIC 与 BETWEEN 相同，只是不要求 AND 左侧的参数小于或等于右侧的参数。如果不是，这两个参数会自动交换，因此总是隐含非空范围。
回答by Juan Garfias
Read the documentation.
阅读文档。
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
I used a query like that:
我使用了这样的查询：
WHERE
(
date_trunc('day',table1.date_eval) = '2015-02-09'
)
or
或者
WHERE(date_trunc('day',table1.date_eval) >='2015-02-09'AND date_trunc('day',table1.date_eval) <'2015-02-09')
Juanitos Ingenier.
华尼托斯·工程师。
