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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: ben(dot)hallert(at)gmail(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Generate a list of (days/hours) between two dates
Date: 2005-06-28 06:38:43
Message-ID: 20050628063843.GA18810@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Jun 27, 2005 at 10:30:38AM -0700, ben(dot)hallert(at)gmail(dot)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/

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Youngblood 2005-06-28 06:49:38 Performance Tuning Best Practices for 8
Previous Message Gregory Youngblood 2005-06-28 05:59:48 Re: Generate a list of (days/hours) between two dates