From: | Michał Roszka <mike(at)if-then-else(dot)pl> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | A note on how to generate a list of months within some period of time. |
Date: | 2010-12-03 21:00:36 |
Message-ID: | 20101203220036.f90bf9eb.mike@if-then-else.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
mike(at)if-then-else(dot)pl
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2010-12-03 23:20:33 | Re: Union Question |
Previous Message | Shaun McCloud | 2010-12-03 17:30:26 | Re: Union Question |