Обсуждение: 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?

Hopefully I'm understanding your question correctly. If so, maybe
this will do what you are wanting.

First, a couple of questions. Do you have this data in a table
already, and are looking to extract information based on the dates?
Or, are you basically wanting something like a for loop so you can
generate the dates between start and stop values?

If the former, and I understand what you are looking to accomplish,
here's one way to do it:

select timestampfield::date::timestamp as "date", count(*) from table
where timestampfield between start and stop group by "date" order by
"date";

should yield:
YYYY-MM-DD 00:00:00        #

for hours, use :

select (substr(timestampfield, 1, 13) || ':00:00')::timestamp as
"hourly", count(*) from table where timestampfield between start and
stop group by "hourly" order by "hourly";

should yield:
YYYY-MM-DD HH:00:00      #

Of course, this assumes your database already has this information.

i hope this helps.
Greg

On Jun 27, 2005, at 10:30 AM, ben.hallert@gmail.com wrote:

> 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
>

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@gmail.com
> Sent: 27 Jun 2005 10:30:38 -0700
> To: pgsql-general@postgresql.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

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/

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@digame.de



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

Thanks for the replies!  I've adopted the generate_series method, it's
absolutely perfect.  I didn't have the dates in a table yet, I needed a
method to generate them from scratch, and this will do nicely.

Thanks again, and hopefully I'll be able to contribute back someday!