pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGenerating dates prior to generate_series
2+ messages / 2 participants
[nested] [flat]
* Generating dates prior to generate_series
@ 2007-04-04 12:00 Roger Tannous <roger77_lb@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Roger Tannous @ 2007-04-04 12:00 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Generating dates prior to generate_series
@ 2007-04-05 15:24 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Roger Tannous <roger77_lb@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2007-04-05 15:24 UTC (permalink / raw)
To: Roger Tannous <roger77_lb@yahoo.com>; +Cc: pgsql-sql
On Wed, 2007-04-04 at 07:00, Roger Tannous wrote:
> 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.
As someone who is still responsible for the care and feeding of a 7.4
database, thanks for the function.
You really should upgrade to the latest 7.3.xx version, as there were
many data loss bugs in earlier 7.3.xx versions that have been fixed.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-04-05 15:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-04 12:00 Generating dates prior to generate_series Roger Tannous <roger77_lb@yahoo.com>
2007-04-05 15:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
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