Date Generator

Snippets

Date Series Generator

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

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;

Usage

-- 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;

Note

PostgreSQL 8.4 provides a generate_series(timestamptz, timestamptz, interval) function which returns a set of timestamptz.