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 = date_trunc('month', current_date); -- 2010-12-01 min_date = '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 = 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 = min_date + '1 month'::interval; EXIT WHEN max_date < min_date; END LOOP; You get the idea. ;) Cheers, -Mike -- Michał Roszka m...@if-then-else.pl -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
