That did the job. Thanks.
Am new to SQL, does not even know that there exists an Operator called OVERLAPS.
Thanks Richard
../rssrik
--- On Tue, 17/3/09, Richard Huxton <d...@archonet.com> wrote:
From: Richard Huxton <d...@archonet.com>
Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and
end timestamps
To: "Srikanth" <rss...@yahoo.co.in>
Cc: pgsql-sql@postgresql.org
Date: Tuesday, 17 March, 2009, 8:36 PM
Srikanth wrote:
> Dear all,
>
> I have a table that records User Login Sessions with two timestamp fields..
> Basically Start of Session and End of a Session (start_ts and end_ts). Each
> row in the table identifies a session which a customer has used.
>
> Data from the table (session):
> -----------------------------
> customer_id | log_session_id | start_ts | end_ts
> -------------+-----------------+----------------------------+----------------------------
> 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008
>16:58:52.665327
> 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008
>22:59:02.770218
> 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009
>14:58:59.989182
> 1006000008 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009
>12:07:15.947509
> 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008
>13:56:58.394577
>
>
> The requirement is as follows,
>
> I have to find out how many User Sessions that were present in any given "1
> HOUR TIME PERIOD". A single User Session can span across many days.
SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (<start-of-hour>,
<end-of-hour>);
> I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain.
I tries googling / searching archives without any success either.
I'd have thought OVERLAPS would be mentioned in the date/time handling
sections of the manual.
--
Richard Huxton
Archonet Ltd
Add more friends to your messenger and enjoy! Go to
http://messenger.yahoo.com/invite/
