Postgresql选择在月范围内的数据

Question

我有一个表，其中一列是日期，格式为'YYYY-MM-DD'。我能否使用select语句获取一个月范围内的所有数据？比如说，我想获取2012年01月xx日至2013年04月xx日的所有数据。因此，我基本上正在寻找像下面给出的SQL查询：

SELECT * FROM table WHERE date IN BETWEEN '2012-01' AND '2013-04' (INVALID QUERY)

由于每个月都以“01”开头，所以我可以修改上面的查询以调整起始条件。

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND '2013-04' (INVALID QUERY)

现在问题出在结束日期上。我必须手动计算给定月份的最后一天，考虑到该月份的长度、闰年等所有因素，因为如果给定日期无效，则查询将失败。所以目前我正在做这样的事情:

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND 'VALID_MONTH_END_DATE' (VALID Query)

我想知道是否有任何方法可以避免这个有效期结束日期的计算？

澄清

我已经考虑过下个月的第一天，但即使这样，我仍然需要应用一些逻辑，比如如果是12月，下个月将会是明年的1月。我想知道是否有可能只使用SQL来解决这个问题？

- Vipin Parakkat

Answer 1

避免使用 BETWEEN 进行日期范围比较是一个好习惯。最好使用 >= 和 <，因为它们对于日期和日期时间列/值都同样适用。

一种方法（如果您可以在外部构建日期）：

WHERE date >= DATE '2012-01-01' 
  AND date < DATE '2013-05-01'      --- first date of the next month

你还可以使用日期算术：

WHERE date >= DATE '2012-01-01' 
  AND date < DATE ('2013-04-01' + INTERVAL '1 MONTH')

或使用 OVERLAPS 运算符：

WHERE (date, date) OVERLAPS
      (DATE '2012-01-01', DATE '2013-05-01')

你还应该阅读Postgres文档：Date/Time Functions and Operators

手册在这里解释了为什么 OVERLAPS 的工作方式是这样的：

每个时间段都被认为代表半开区间 start <= time < end，除非start和end相等，表示单个时间点。这意味着仅在一个端点相同的两个时间段不会重叠。

- ypercubeᵀᴹ

---

当我知道最后一个日期时，重叠函数运行良好。但当最后一个月是十二月时，我就卡住了。 - Vipin Parakkat

你的参数从哪里获取？是从应用程序还是从网络中获得？还是从另一个查询中获取？它们作为字符串还是整数（年份、月份）？ - ypercubeᵀᴹ

在给日期添加“间隔”时必须小心。结果是一个“时间戳”。在上面的示例中可以工作。如果有必要，将结果转换为“日期”。无论涉及的月份实际天数如何，添加间隔“n个月”始终导致同一天 - 或者当结果月份天数较少时，导致最大可用天数。要添加确切数量的天数，请将“整数”添加到“日期”中，这将导致“日期”。 - Erwin Brandstetter

大多数现代优化器不是会将“between”翻译成“>= and <=”吗？使用between时，几乎没有任何性能损失，除非您使用的是旧版本。 - sam yi

+1 对于重叠部分的解决方案是有帮助的，但这些选项仍然不能满足操作者的需求。如果提供了两个日期（开始日期和结束日期）...你需要找出月底是哪一天？ - sam yi

Answer 2

这在报告环境中非常常见。我创建了几个函数来满足这些日期操作的需求。

CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

然后，您可以使用...

WHERE date >= '2012-01-01' 
  AND date < fn_getlastofmonth('2013-04-01') 

- sam yi

---

或者是一个带有相同签名和这个函数体的 SQL 函数：select date_trunc('month', $1) + interval '1 month - 1 day'; - Stefanov.sm

Answer 3

我没有尝试过这个，但值得一试。

SELECT * 
FROM some_table 
WHERE some_date 
BETWEEN '2012-01-01' AND date('2013-04-01') - integer '1'

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

- house9

---

ypercube的回答更好，不需要使用between。 - house9

Answer 4

所有上面的答案都提供了一种有效的解决方案，但在某种程度上还是不完整的。由于我正在寻找一个仅使用SQL的解决方案（没有函数），因此我将结合上述解决方案中的最佳技巧。

对于我的问题来说，理想的解决方案应该是：

SELECT * FROM table
WHERE date >= '2012-01-01' AND date < date('2013-04-01') + interval '1 month'

编辑

我在这里没有使用重叠函数，因为我将默认的开始和结束日期值传递为“纪元”和“现在”。如果用户没有指定任何时间范围，则查询变为：

SELECT * FROM table
WHERE date >= 'epoch' AND date < 'now'

Overlap函数无法处理“epoch”和“now”，并且在上述代码对两种情况都能正常工作的情况下会出现SQL错误。

附注：我已经为所有正确回答并引导我找到这个解决方案的答案点赞了。

- Vipin Parakkat

---

我认为你需要删除“- interval '1 day'”这部分。尝试使用表格上的“'2013-04-30'”日期作为现状。 - ypercubeᵀᴹ

我正在寻找每月的数据。假设我需要获取某年某月到yyyy-mm的所有数据。我的结束日期始终为yyyy-mm-01，我会加上一个月并减去一天。 - Vipin Parakkat

只需测试查询：SELECT * FROM table WHERE (date, date) OVERLAPS('2012-01-01' , date('2013-04-01') + interval '1 month' - interval '1 day')，当表中有一个值为2013-04-30的date时，它是否被返回？ - ypercubeᵀᴹ

嗨，我刚刚检查了一下。它不起作用。感谢您指出这个问题。这是由于重叠函数的性质造成的。 - Vipin Parakkat

Answer 5

SET search_path=tmp;

DROP TABLE zdates;
CREATE TABLE zdates
        ( zdate timestamp NOT NULL PRIMARY KEY
        , val INTEGER NOT NULL
        );
-- some data
INSERT INTO zdates(zdate,val)
SELECT s, 0
FROM generate_series('2012-01-01', '2012-12-31', '1 day'::interval ) s
        ;

UPDATE zdates
SET val = 1000 * random();

DELETE FROM zdates
WHERE random() < 0.1;

-- CTE to round the intervals down/up to the begin/end of the month
WITH zope AS (
        SELECT date_trunc('month', zdate)::date AS zbegin
        ,  date_trunc('month', zdate+interval '1 month')::date AS zend
        , val AS val
        FROM zdates
        )
SELECT z.zbegin
        , z.zend
        , COUNT(*) AS zcount
        , SUM(val) AS zval
FROM zope z
GROUP BY z.zbegin, z.zend
ORDER BY z.zbegin, z.zend
        ;

结果：

CREATE TABLE
INSERT 0 366
UPDATE 366
DELETE 52
   zbegin   |    zend    | zcount | zval  
------------+------------+--------+-------
 2012-01-01 | 2012-02-01 |     28 | 13740
 2012-02-01 | 2012-03-01 |     28 | 14923
 2012-03-01 | 2012-04-01 |     26 | 13775
 2012-04-01 | 2012-05-01 |     25 | 11880
 2012-05-01 | 2012-06-01 |     25 | 12693
 2012-06-01 | 2012-07-01 |     25 | 11082
 2012-07-01 | 2012-08-01 |     26 | 13254
 2012-08-01 | 2012-09-01 |     28 | 13632
 2012-09-01 | 2012-10-01 |     28 | 16461
 2012-10-01 | 2012-11-01 |     23 | 12622
 2012-11-01 | 2012-12-01 |     24 | 12554
 2012-12-01 | 2013-01-01 |     28 | 14563
(12 rows)

- wildplasser