Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

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 <dev(at)archonet(dot)com> wrote:

From: Richard Huxton <dev(at)archonet(dot)com>
Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
To: "Srikanth" <rssrik(at)yahoo(dot)co(dot)in>
Cc: pgsql-sql(at)postgresql(dot)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/