CODEWITHSUNDEEP
datepostgresqlseries
Arthur
Arthur

Reputation: 3428

Creating a date series in postgresql 8.3

I am trying to create a series of dates from a fixed date in past to current date, in month increments. I know this is possible in 8.4 with a new feature but i am stuck with 8.3 for now.

I feel I am going down a rabbit hole here as I have this sql to get me monthly increments

SELECT  date('2008-01-01') + (to_char(a,'99')||' month')::interval as date FROM generate_series(0,20) as a;

I am then trying to extract months and years from the interval of current date - fixed date

SELECT extract(   month from interval (age(current_date, date('2008-01-01')))     );

but im beginning to think this is a silly way to get the desired date series.

Upvotes: 2

Views: 1678

Answers (2)

remo
remo

Reputation: 780

In case someone would need e.g. 3 hour interval inside given date range:

SELECT ('2013-01-01 0:0'::timestamp
       + interval '1 hour' * generate_series(0, ('2013-02-01'::date - '2013-01-01'::date)*24, 3))::timestamp;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

Could work like this:

SELECT ('2008-01-01 0:0'::timestamp
       + interval '1 month' * generate_series(0, months))::date
FROM   (
   SELECT (extract(year from intv) * 12
          + extract(month from intv))::int4 AS months
   FROM   (SELECT age(now(), '2008-01-01 0:0'::timestamp) as intv) x
   ) y

Upvotes: 4

Related Questions