Skip site navigation (1) Skip section navigation (2)

Re: Count rows group by time intervals

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: "Richard Broersma Jr" <rabroersma(at)yahoo(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Count rows group by time intervals
Date: 2007-05-14 07:57:38
Message-ID: 1c23c8e70705140057p7deafa15pa169d7c30b4aa22f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Another way is to use an auxillary table to join on your required date
> range.
>
>     SELECT A.theme, A.receiver, COUNT(A.date),
>            Time_range.date_start, Time_range.date_end
>       FROM Your_table A
> INNER JOIN ( SELECT ( SELECT MIN( date ) FROM Your_table ) + x * INTERVAL
> '1 day' AS date_start,
>                     ( SELECT MIN( date ) FROM Your_table ) + 2 + x *
> INTERVAL '1 day' AS date_end,
>                FROM Generate_series( 1, ( SELECT MAX( date ) FROM
> Your_table ) -
>                                         ( SELECT MIN( date ) FROM
> Your_table ), 2 )
>            ) AS Time_range( date_start, date_end )
>         ON B.date_start <= A.date AND B.date_end > A.date
>   GROUP BY A.theme, A.receiver, Time_range.date_start,
> Time_range.date_end;


Hi Richard,

I think your solution is very good and elegant, but I cannot call
generate_series()
because in the 7.4.2 version of postgres ( I use this version ) this
function doesn't
exist.

Many thanks,
         Loredana

In response to

pgsql-novice by date

Next:From: David FleglDate: 2007-05-14 08:23:19
Subject: How to use 2PC?
Previous:From: Loredana CurugiuDate: 2007-05-14 07:49:33
Subject: Re: Fw: Count rows group by time intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group