Re: Generate a list of (days/hours) between two dates

On Mon, Jun 27, 2005 at 10:30:38AM -0700, ben.hallert@gmail.com wrote:
>
> I'd like to make a query that would return a list of every trunc'd
> TIMESTAMPs between two dates.  For example, I'd want to get a list of
> every date_trunc('hour',whatever) between 6-1-2005 and 6-10-2005 and
> get a list that looks like:
>
> 6-1-2005 00:00:00
> 6-1-2005 01:00:00
> 6-1-2005 02:00:00

Something like this?

SELECT '2005-06-01 00:00:00'::timestamp + x * interval'1 hour'
FROM generate_series(0, 9 * 24) AS g(x);

Another possibility would be to write your own set-returning function
that takes the start and end timestamps and a step value.

> Conversely, I want to generate a list of every day between two dates,
> like:
>
> 6-1-2005 00:00:00
> 6-2-2005 00:00:00
> 6-3-2005 00:00:00

SELECT '2005-06-01 00:00:00'::timestamp + x * interval'1 day'
FROM generate_series(0, 9) AS g(x);

generate_series() is a function in PostgreSQL 8.0 and later, but
it's trivial to write in earlier versions using PL/pgSQL.

http://www.postgresql.org/docs/8.0/static/functions-srf.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/