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

This might be helpful,

select current_date + s.t as dates from generate_series(0,5) as s(t);
dates
------------
2005-06-28
2005-06-29
2005-06-30
2005-07-01
2005-07-02
2005-07-03
(6 rows)

with regards,
S.Gnanavel

> -----Original Message-----
> From: ben(dot)hallert(at)gmail(dot)com
> Sent: 27 Jun 2005 10:30:38 -0700
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Generate a list of (days/hours) between two dates
>
> Hi guys,
>
> I've scoured the date/time functions in the docs as well as
> google-grouped as many different combinations as I could think of to
> figure this out without asking, but I'm having no luck.
>
> 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
> etc
>
> 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
>
> I know there's gotta be some way to do this in a SELECT function, but
> I'm running into a brickwall. I'm trying to take some of my date
> handling logic out of code and use the db engine so I can spend less
> time developing/maintaining code when mature date handling already
> exists in a resource I've already got loaded.
>
> Any thoughts?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq