Date Generator

From PostgreSQL wiki
Jump to navigationJump to search

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.