在 PostgreSQL 中获取范围内的日期列表

我想得到的日期之间的两个日期(包括他们)在一个 PostgreSQL 数据库的列表。例如，如果我有:

  • 开始日期: 2012年6月29日
  • 二零一二年七月三日

那么结果应该是:

29 june 2012
30 june 2012
1 july 2012
2 july 2012
3 july 2012

在 PostgreSQL 中实现这一点的最佳方法是什么？

谢谢。

63415 次浏览
小开

这个应该可以:

select date '2012-06-29' + i
from generate_series(1, (select date '2012-07-3' - date '2012-06-29')) i

如果你不想在子选项中重复 start _ date，那么事情就有点复杂了:

with min_max (start_date, end_date) as (
values (date '2012-06-29', date '2012-07-3')
), date_range as (
select end_date - start_date as duration
from min_max
)
select start_date + i
from min_max
cross join generate_series(1, (select duration from date_range)) i;

(参见 Maniek 对“无重复”问题的更好解答)

小开

如果您已经有要查询的数据库:

SELECT
TO_CHAR(date_column,'DD Mon YYYY')
FROM
some_table
WHERE
date_column BETWEEN '29 Jun 2012' AND '3 JUL 2012'


GROUP BY date_column
ORDER BY date_column

这将导致:

"29 Jun 2012"
"30 Jun 2012"
"01 Jul 2012"
"02 Jul 2012"
"03 Jul 2012"
小开

这个 PLpg/SQL 函数可以解决这个问题:

CREATE OR REPLACE FUNCTION getDateList(date1 date, date2 date)
RETURNS SETOF date AS
$BODY$
DECLARE
count integer;
lower_limit integer :=  0;
upper_limit integer :=  date2 - date1;
BEGIN
FOR count IN lower_limit..upper_limit LOOP
RETURN NEXT date1 + count;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
小开

如果日期范围应来自表表达式，则可以使用以下构造:

DROP TABLE tbl ;
CREATE TABLE tbl (zdate date NOT NULL );
INSERT INTO tbl(zdate) VALUES( '2012-07-01') , ('2012-07-09' );


WITH mima AS (
SELECT MIN(zdate)::timestamp as mi
, MAX(zdate)::timestamp as ma
FROM tbl
)
SELECT generate_series( mima.mi, mima.ma, '1 day':: interval):: date
FROM mima
;

之所以需要强制转换，是因为 generate_ Series ()不接受日期参数。

小开
最佳答案 
select CURRENT_DATE + i
from generate_series(date '2012-06-29'- CURRENT_DATE,
date '2012-07-03' - CURRENT_DATE ) i

甚至更短:

select i::date from generate_series('2012-06-29',
'2012-07-03', '1 day'::interval) i
小开

对于这样的事情，在系统中有一个日期表通常很方便。

与动态生成日期相比，它们就像数字表一样非常有用，而且使用起来非常快，特别是当您扩展到大型数据集时。

这样的日期表从1900年到2100年将是非常小的，所以没有太多的存储开销。

编辑: 不知道为什么会被否决，这可能是最好的性能。而且它还有很多其他的优势。想将订单与季度业绩数字联系起来吗？这是两个表之间的一个简单链接。(命令。订购日期-> 日期。日期-> 日期。季度-> 表现总数。季度)等。处理工作日也是如此，比如一个月的最后一个工作日，或者上个月的第一个星期二。就像数字表一样，我强烈推荐它们！

小开

作为 timestamp:

select generate_series('2012-06-29', '2012-07-03', '1 day'::interval);


generate_series
------------------------
2012-06-29 00:00:00-03
2012-06-30 00:00:00-03
2012-07-01 00:00:00-03
2012-07-02 00:00:00-03
2012-07-03 00:00:00-03

或投出 date:

select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date;


generate_series
-----------------
2012-06-29
2012-06-30
2012-07-01
2012-07-02
2012-07-03
小开 
select generate_series('2012-06-29', '2012-07-03', '1 day'::interval)::date;

提交答案