Generating a Range of Values
For example, lets generate a set of months, from 2010-01-01 to 2011-01-01.
In SQL Server:
1 WITH t AS (
2 SELECT CONVERT(date, '2010-01-01') AS d
3 UNION ALL
4 SELECT DATEADD(MONTH, 1, d) FROM t WHERE d < '2010-12-01'
5 )
6 SELECT * FROM t ORDER BY d;
Same in PostgreSQL:
1 WITH RECURSIVE t AS (
2 SELECT '2010-01-01'::timestamp AS d
3 UNION ALL
4 SELECT d + '1 months' FROM t WHERE d < '2010-12-01'
5 )
6 SELECT * FROM t ORDER BY d
In PostgreSQL, you can also use generate_series() function, but it’s useful only in these basic cases:
1 SELECT * FROM generate_series('2010-01-01'::timestamp, '2010-12-01', '1 months');
