在PostgreSQL中生成两个日期之间的时间序列 [英] Generating time series between two dates in PostgreSQL

问题描述

我有一个这样的查询，很好地在两个给定日期之间生成一系列日期：

I have a query like this that nicely generates a series of dates between 2 given dates:

select date '2004-03-07' + j - i as AllDate 
from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i,
     generate_series(0, extract(doy from date '2004-08-16')::int - 1) as j

它在 2004-03-07 和 2004-08-16 之间生成162个日期，这就是我想要的。但是这个代码的问题是，当两个日期来自不同的年份时，它不会给出正确的答案，例如当我尝试 2007-02-01 和 2008-04-01 。任何人都有任何想法如何推广或替代？

It generates 162 dates between 2004-03-07 and 2004-08-16 and this what I want. But the problem with this code is that it wouldn't give the right answer when the two dates are from different years, for example when I try 2007-02-01 and 2008-04-01. Anyone has any idea how to generalize it or an alternative?

推荐答案

可以不转换到/从int（但是/从时间戳来代替）

Can be done without conversion to/from int (but to/from timestamp instead)

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

这篇关于在PostgreSQL中生成两个日期之间的时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！