<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">That did the job.
Thanks.<br/>Am new to SQL, does not even know that there exists an Operator called OVERLAPS.<br /><br />Thanks Richard
<br/>./rssrik<br />--- On <b>Tue, 17/3/09, Richard Huxton <i><dev@archonet.com></i></b> wrote:<br /><blockquote
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From: Richard Huxton
<dev@archonet.com><br/>Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end
timestamps<br/>To: "Srikanth" <rssrik@yahoo.co.in><br />Cc: pgsql-sql@postgresql.org<br />Date: Tuesday, 17
March,2009, 8:36 PM<br /><br /><div class="plainMail">Srikanth wrote:<br />> Dear all,<br />> <br />> I have a
tablethat records User Login Sessions with two timestamp fields. Basically Start of Session and End of a Session
(start_tsand end_ts). Each row in the table identifies a session which a customer has used. <br />> <br />> Data
fromthe table (session):<br />> -----------------------------<br />> customer_id | log_session_id |
start_ts | end_ts<br />>
-------------+-----------------+----------------------------+----------------------------<br/>> 1006100716 |
209571229340224| 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327<br />> 1006100789 | 112061228488202 |
05/12/200820:13:32.773065 | 09/12/2008 22:59:02.770218<br />> 1006100888 | 214221233045949 | 27/01/2009
14:15:16.289626| 27/01/2009 14:58:59.989182<br />> 1006000008 | 205221236839534 | 12/03/2009 12:02:15.947509 |
12/03/200912:07:15.947509<br />> 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008
13:56:58.394577<br/>> <br />> <br />> The requirement is as follows,<br />> <br />> I have to find out
howmany User Sessions that were present in any given "1 HOUR TIME PERIOD". A single User Session can span across many
days.<br/><br />SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (<start-of-hour>,<br
/><end-of-hour>);<br/><br />> I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain.<br />I
triesgoogling / searching archives without any success either.<br /><br />I'd have thought OVERLAPS would be mentioned
inthe date/time handling<br />sections of the manual.<br /><br />-- <br /> Richard Huxton<br /> Archonet Ltd<br
/></div></blockquote></td></tr></table><br/><hr size="1" /> Add more friends to your messenger and enjoy! <a
href="http://in.rd.yahoo.com/tagline_messenger_6/*http://messenger.yahoo.com/invite/">Invite them now.</a>
