我试图查询我的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查询
发布评论