From PostgreSQL wikiJump to navigationJump to search
Date Series Generator
Works with PostgreSQL
by Scott Bailey 'Artacus'
When working with scheduling applications, having a date relation (a table of dates) is pretty much a requirement. However, someone challenged me on why I was materializing it when Postgres could have generated it on the fly with a set returning function.
Some testing showed that generating it on the fly performed as well as reading from disk, even when all of the pieces I was filtering on were broken out and indexed.
CREATE OR REPLACE FUNCTION generate_dates( dt1 date, dt2 date, n int ) RETURNS SETOF date AS $$ SELECT $1 + i FROM generate_series(0, $2 - $1, $3) i; $$ LANGUAGE sql IMMUTABLE;
-- Select even days in odd months SELECT dt, to_char(dt, 'FMDAY, DD MON YYYY') FROM generate_dates('2009-01-01', '2009-12-31', 1) dt WHERE MOD(EXTRACT(MONTH FROM dt)::int,2) = 1 AND MOD(EXTRACT(DAY FROM dt)::int,2) = 0;
PostgreSQL 8.4 provides a
generate_series(timestamptz, timestamptz, interval) function which returns a set of