日期范围之间的PostgreSQL查询 [英] Postgresql query between date ranges

问题描述

我试图查询我的postgresql数据库，以返回日期在特定月份和年份的结果。换句话说，我想要一个月年的所有值。

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.

到目前为止，我唯一能做到的方法是这样：

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?

谢谢

推荐答案

使用日期（和时间），如果您使用> = start AND<结束。

With dates (and times) many things become simpler if you use >= start AND < end.

例如：

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.

结束日期也得到了简化；只需添加一个月即可。不用担心28、30、31等。

The end date is also simplified; just add exactly one month. No messing about with 28th, 30th, 31st, etc.

此结构还具有能够保持索引使用的优势。

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 not use 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).

这篇关于日期范围之间的PostgreSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！