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

["Hakan Kocaman" <Hakan.Kocaman@xxxxxxxxx>]

Hi, 
hier the same for minutes.
Just change the intervall to 'hour' and the series-count to '24' :

select 
	current_date || ' ' || mytimequery.mytime
	as dates 
from 
	(select 
		(TIME '00:00:00' + myintervalquery.myinterval)::time as mytime
	 from 
		(select 
			(s.t ||' minute')::interval as myinterval
		from 
			generate_series(0,1439) as s(t)
		)
		as myintervalquery
	
	)
	as mytimequery;

Best regards

Hakan Kocaman

Software-Developer
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98

Email: hakan.kocaman@xxxxxxxxx



> -----Original Message-----
> From: pgsql-general-owner@xxxxxxxxxxxxxx 
> [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of 
> Gnanavel Shanmugam
> Sent: Tuesday, June 28, 2005 7:45 AM
> To: ben.hallert@xxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
> Subject: 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.hallert@xxxxxxxxx
> > Sent: 27 Jun 2005 10:30:38 -0700
> > To: pgsql-general@xxxxxxxxxxxxxx
> > Subject:  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
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq