[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
From: Srikanth <rssrik () yahoo ! co ! in>
Date: 2009-03-18 16:14:29
Message-ID: 44549.51982.qm () web94613 ! mail ! in2 ! yahoo ! com
[Download RAW message or body]
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@archonet.com> wrote:
From: Richard Huxton <dev@archonet.com>
Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end \
timestamps
To: "Srikanth" <rssrik@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/
[Attachment #3 (text/html)]
<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: \
inherit;">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 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. \
<br>> <br>> Data from the 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/2008 \
20: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/2009 12: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 \
how many 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 tries googling / searching \
archives without any success either.<br><br>I'd have thought OVERLAPS would be \
mentioned in the date/time handling<br>sections of the manual.<br><br>-- <br> \
Richard Huxton<br> Archonet Ltd<br></div></blockquote></td></tr></table><br> \
<!--6--><hr size=1></hr> 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>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic