Date: Fri, 3 Dec 2010 22:00:36 +0100
From: =?UTF-8?B?TWljaGHFgg==?= Roszka <mike@if-then-else.pl>
To: pgsql-sql@postgresql.org
Subject: A note on how to generate a list of months within some period of
 time.
Message-Id: <20101203220036.f90bf9eb.mike@if-then-else.pl>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello,

I am referring to an old message and it's follow-ups.  Today I went
through a similar problem and solved it in a different way.  I would
like to share my approach here to help others who might be searching
for it.  I think it is very simple and flexible.

The old message is here:

http://archives.postgresql.org/pgsql-sql/2010-03/msg00084.php

Esentially, I need to generate an ordered list of months, like:

2010-12
2010-11
2010-10
...
2008-04
2008-03
2008-02

First, let's specify the limits: the current date (max_date)
and some date in the past (min_date).  Additionally let's have them
rounded down to the first day of the month.

max_date =3D date_trunc('month', current_date); -- 2010-12-01
min_date =3D '2008-02-01'::date; -- 2008-02-01

You might need the min_date to be calculated based on the actual data:

SELECT INTO min_date date_trunc('month', mytable.created)
FROM mytable ORDER BY mytable.created ASC LIMIT 1;

mytable.created is a column in mytable of type date (or similar) and
here we select the lowest value.  Replace ASC with DESC to select the
highest one.

Let's generate a descending list:

LOOP
    RAISE NOTICE '%', max_date;
    max_date =3D max_date - '1 month'::interval;
    EXIT WHEN max_date < min_date;
END LOOP;

Replace the RAISE NOTICE statement with one, that fits your needs best.
Note, that the EXIT WHEN statement solves the case when the initial
value of max_date is already lower than the one of min_date.

If you need an ascending list, use min_date instead of max_date and
modify it by addition instead of subtraction.  Leave the EXIT WHEN
statement unchanged:

LOOP
    RAISE NOTICE '%', min_date;
    min_date =3D min_date + '1 month'::interval;
    EXIT WHEN max_date < min_date;
END LOOP;

You get the idea. ;)

Cheers,

    -Mike

--=20
Micha=C5=82 Roszka
mike@if-then-else.pl