Date: Thu, 2 Apr 2009 09:54:04 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: James Kitambara <jameskitambara@yahoo.co.uk>
Cc: Srikanth <rssrik@yahoo.co.in>, pgsql-sql@postgresql.org
Subject: Re: SQL to Check whether "AN HOUR PERIOD" is between start
	and end timestamps
Message-ID: <20090402135404.GD3163@alvh.no-ip.org>
References: <19947.64230.qm@web94606.mail.in2.yahoo.com>
	<49BFBC61.9020006@archonet.com>
	<849335.93192.qm@web27903.mail.ukl.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <849335.93192.qm@web27903.mail.ukl.yahoo.com>
User-Agent: Mutt/1.5.18 (2008-05-17)

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.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.