Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jun 27, 2005 at 10:30:38AM -0700, ben.hallert@xxxxxxxxx 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/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux