Message-ID: <3F1410CB.9020504@openratings.com>
Date: Tue, 15 Jul 2003 10:33:47 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Count dates distinct within an interval
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi, everybody!

I was trying to formulate a sql query, that I now think is impossible :-(
I just thought, I'd run it by you before I give up - perhaps, you guys 
would have some ideas...

Suppose, I have a table like this

create table test
(
   stuff int,
   stamp timestamp
);

Now, I want to count the occurences of each value of stuff in the table, 
but so that entries within 24 hours from each other count as one...
The closest I could think of is:

select stuff, count (distinct date_trunc ('day', stamp)) from test group 
by stuff;

This doesn't do exactly what I need though - for example, if one entry 
is one minute before midnight, and the other one is two minutes later, 
they'd get counted  as 2, and what I am looking for is the way to get 
them collapsed into one as long as they are less then 24 hours apart...

Now, I am pretty sure, it is simply impossible to do what I want with 
count (distinct...) because my 'equality' is not transitive - for 
example, three entries, like
A = 2001 - 01- 01 20:20:00
B = 2001 - 01 - 02 20:19:00
C = 2001 - 01 - 02 20:21:00

Should be counted as *two* (A === B, and B === C, but *not* A === C)...

Also, I could certainly write a simple function, that would get all the 
entries in order, and scan through them, counting according to my rules...
But I was hoping to find some way to do this in plain sql though...

Any ideas?

Thanks!

Dima