From: Roger Tannous <roger77_lb@yahoo.com>
To: PostgreSQL SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Generating dates prior to generate_series
Date: Wed, 4 Apr 2007 05:00:56 -0700 (PDT)
Message-ID: <580286.82124.qm@web90602.mail.mud.yahoo.com> (raw)
I'm using PostgreSQL version 7.3.2, and generate_series() is not
available, so this is a function to generate a series dates.
The function goes backwards if the second argument is less than the first
one. Check the two select statements at the end.
Best Regards,
Roger Tannous.
CREATE FUNCTION date_range(VARCHAR, VARCHAR) RETURNS SETOF DATE AS '
DECLARE
dateStart ALIAS FOR $1;
dateEnd ALIAS FOR $2;
forwardSteps BOOLEAN := true;
tmpDate DATE;
BEGIN
IF (to_date(dateStart, ''YYYY-mm-dd'') >
to_date(dateEnd, ''YYYY-mm-dd'')) THEN
forwardSteps := false;
END IF;
tmpDate := to_date(dateStart, ''YYYY-mm-dd'');
WHILE ((forwardSteps AND tmpDate <=
to_date(dateEnd, ''YYYY-mm-dd'')) OR (NOT forwardSteps AND tmpDate >=
to_date(dateEnd, ''YYYY-mm-dd''))) LOOP
RETURN NEXT tmpDate;
IF forwardSteps THEN
tmpDate := tmpDate +
interval ''1 day'';
ELSE
tmpDate := tmpDate -
interval ''1 day'';
END IF;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
select * FROM date_range('2007-01-03', '2007-03-20');
select * FROM date_range('2007-04-03', '2007-03-20');
____________________________________________________________________________________
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roger77_lb@yahoo.com
Subject: Re: Generating dates prior to generate_series
In-Reply-To: <580286.82124.qm@web90602.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox