Re: Fw: Count rows group by time intervals

Funny thing.
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in
between...which is another integer, I guess. and '%' is the modular
operator. Doesnt it work like this on yours?

Which version are you using?

try

SELECT (date - '2007-01-01') % 2
FROM table

to see if you get zeroes and ones .

Cheers,
Oliveiros

2007/5/9, Loredana Curugiu <loredana(dot)curugiu(at)gmail(dot)com>:
>
>
> > You need a query that returns the number of receivers on each theme, in
> > two days intervals, is this correct?
> >
>
> yes, this is correct. Actually the interval in days is variable ( it would
> specified using PreparedStatemtent ).
> Sorry I didn't say from the begining.
>
> Please try something like this. I am not sure if it works, because I don't
> > have a table like yours.
> > I am assuming your table is called table
> >
> > SELECT a."date",a."theme",a.receiver,COUNT(*)
> > FROM table a
> > INNER JOIN table b
> > ON a."theme" = b."theme" AND a.receiver = b.receiver
> > WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
> > GROUP BY a."date",a."theme",a.receiver
> > HAVING (a."date" - '2007-01-01' ) % 2 = 0
> > ORDER BY a."date"
> >
>
> The query above it's not working, I've got the following error:
> ERROR: operator does not exist: interval % integer
> HINT: No operator matches the given name and argument type(s). You
> may need to add explicit type casts.
>
>
>
>