To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: SQL to Check whether "AN HOUR PERIOD" is between start and end
	timestamps
Date: 10 Apr 2009 22:41:54 GMT
Organization: Dis (not Dat) Organisation
Lines: 38
Message-ID: <grohvi$f4t$1@reversiblemaps.ath.cx>
References: <19947.64230.qm@web94606.mail.in2.yahoo.com>
	<49BFBC61.9020006@archonet.com>
	<849335.93192.qm@web27903.mail.ukl.yahoo.com>
	<20090402135404.GD3163@alvh.no-ip.org>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-2 (Linux)

On 2009-04-02, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> James Kitambara wrote:
>> Dear Srikanth,
>> You can solve your problem by doing this
>> 
>> THE SQL IS AS FOLLOWS
>>   ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE TABLE NAME time_interval
>> 
>>  COUNT (*) FROM  
>>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval
>>      where end_ts-start_ts >= '1 hour'
>>      and '2008-12-07 07:59:59' between start_ts and end_ts)
>> AS COUNT ;
>
> Another way to phrase the WHERE clause is with the OVERLAPS operator,
> something like this:
>
> WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 08:59:59')
>
> What I'm not so sure about is how optimizable this construct is.
>

http://www.postgresql.org/docs/8.3/interactive/xindex.html
if you gave the apropriate GIST index on (start_ts, end_ts) the
overlaps may be optimisable. the subquery will run to completion 
and count will count the results. - but this form gives different results.

beter to do 

select COUNT (*) AS COUNT FROM time_interval
  WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 08:59:59')

or 
 
select COUNT (*) AS COUNT FROM time_interval
  where end_ts-start_ts >= '1 hour'
  and '2008-12-07 07:59:59' between start_ts and end_ts;