Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CF1A6632A38 for ; Thu, 2 Apr 2009 09:37:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53162-03 for ; Thu, 2 Apr 2009 09:37:55 -0300 (ADT) X-Greylist: delayed 00:06:38.560446 by SQLgrey-1.7.6 Received: from web27903.mail.ukl.yahoo.com (web27903.mail.ukl.yahoo.com [217.146.182.53]) by mail.postgresql.org (Postfix) with SMTP id 22424632826 for ; Thu, 2 Apr 2009 09:37:54 -0300 (ADT) Received: (qmail 93308 invoked by uid 60001); 2 Apr 2009 12:31:13 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1238675473; bh=hGClic5ViDXmECxQze9fyjCexXj6RQv4hZsYGmGNHKU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=mF4OfzK/XqEOelZeolRDc8pA0aJg9/qCUFzXEPl5Peu5Lra9fcElm1C8+TMLVgRLuA3rCGqqCOe4+JoMGZ2khJPC8loy/QppoLVXPJlKEP2e3w/TnTmd6zCCGy9iFMmbVnyn+nHYB5xoiynOvh6Z8w2RXjMAMhk3JR/D13u3WBM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=X7BnWzVuJcj60Eb5aHBaKJUJFP+tLIcO6haFcNRHQNlnj/mK5VJLPPUNFL2sFhPQEqDRpHCt38FwLK2rc4S7gXkKh0e7HNXKI7wjgwpVSm2lEUvjM9SCt4ZuGr7ECE5A6QjNzjCFMjE8PMVtj3mLBOWYd6teZmY+qrpqLFNTrXM=; Message-ID: <849335.93192.qm@web27903.mail.ukl.yahoo.com> X-YMail-OSG: ABwburEVM1kUBBwqrabHrUYJWLUmRTCC51S2wpKHVjEJQwPfalBpUJ2ORhpJ0JPZI3ScTkwhHAKSiSMOda6J0tqdlQPgIrio1oWZz8.4wSf7s.ifqhTHLWsFtVFw7H8ZTVCVmkRTwDNOq_fL11QTOlNMjo36gLlF70Kh0fObC8BYBnzqSbKvd3ikgFWq338LE65rUdCzA7N9iQukU_joLOYJ_FuJxL84cZkqapBYWD7No5oSLrex1_k9ZADZz_52v0_kWZ.vcfhiWNXR8aPSwyLuW1_V9XNv6RvmilN_ywB7O06JtKiWLqcD9DMMgNOqkjVvceMQoswXoTw- Received: from [196.46.105.168] by web27903.mail.ukl.yahoo.com via HTTP; Thu, 02 Apr 2009 12:31:13 GMT X-Mailer: YahooMailRC/1277.32 YahooMailWebService/0.7.289.1 References: <19947.64230.qm@web94606.mail.in2.yahoo.com> <49BFBC61.9020006@archonet.com> Date: Thu, 2 Apr 2009 12:31:13 +0000 (GMT) From: James Kitambara Subject: Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps To: Srikanth Cc: pgsql-sql@postgresql.org In-Reply-To: <49BFBC61.9020006@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1030704148-1238675473=:93192" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/9 X-Sequence-Number: 32355 --0-1030704148-1238675473=:93192 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Dear Srikanth,=0AYou can solve your problem by doing this=0A=0ATHE SQL IS A= S FOLLOWS=0A=A0 ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:5= 8:59 AND THE TABLE NAME time_interval=0A=0A=A0COUNT (*) FROM =A0=0A=A0=A0= =A0 (select=A0customer_id, log_session_id, start_ts, end_ts=A0, end_ts-star= t_ts as "Interval" from time_interval=0A=A0=A0=A0 =A0where end_ts-start_ts = >=3D '1 hour'=0A=A0=A0=A0=A0 and '2008-12-07 07:59:59' between start_ts and= end_ts)=0AAS COUNT=A0;=0A=0A=A0=0A----------------------------------------= --ORGINAL MESSAGE----------------------------------------------------------= ----=0A=0A=0A________________________________=0AFrom: Richard Huxton =0ATo: Srikanth =0ACc: pgsql-sql@postgresql= .org=0ASent: Tuesday, 17 March, 2009 18:06:09=0ASubject: Re: [SQL] SQL to C= heck whether "AN HOUR PERIOD" is between start and end timestamps=0A=0ADear= all,=0AI have a table that records User Login Sessions with two timestamp = fields. =0ABasically Start of Session and End of a Session (start_ts and en= d_ts). Each row in the table identifies a session which a customer has used= .=A0 =0AData from the table (session):=0A-----------------------------=0A= =A0customer_id | log_session_id=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 start_ts=A0= =A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 end_ts=0A---------= ----+-----------------+----------------------------+-----------------------= -----=0A=A01006100716=A0 | 209571229340224 | 15/12/2008 16:53:52.665327 | 1= 5/12/2008 16:58:52.665327=0A=A01006100789=A0 | 112061228488202 | 05/12/2008= 20:13:32.773065 | 09/12/2008 22:59:02.770218=0A=A01006100888=A0 | 21422123= 3045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182=0A=A01006= 000008=A0 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07= :15.947509=0A=A01006100825=A0 | 112331229068049 | 12/12/2008 13:17:37.22924= 9 | 12/12/2008 13:56:58.394577=0A=0AThe requirement is as follows,=0AI have= to find out how many User Sessions that were present in any given "1 HOUR = TIME PERIOD".=A0 A single User Session can span across many days.=0AExample= :=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 start_ts=A0=A0=A0=A0=A0=A0=A0=A0= =A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 end_ts=0A=A0=A0=A0 05/12/2008 20:13:32.= 773065 | 09/12/2008 22:59:02.770218=0A-------------------------------------= ----------------------------------------------------------------=0ALet me e= xplain a scenario, =0AI have to find out the number of sessions during the = interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.=0A=0AIf I have to = find the number of sessions present at any instant time say '07/01/2009 11:= 25:25', i can easily find out using the following Query, =0Aselect count(lo= g_session_id) from session where '07/01/2009 11:25:25' between start_ts and= end_ts ;=0ABut, I have to find the number of sessions present during the "= HOUR INTERVAL" '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.=0AI tried us= ing wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googlin= g / searching archives without any success either.=0AI feel this is a gener= al requirement and this topic should have already been discussed.=0ACould s= omeone help me solve this please ?=A0 Any lead would do, like some special = postgres-function or any other means.=0AMany Thanks,=0A=0A=0A --0-1030704148-1238675473=:93192 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
=0A
Dear Srikanth,
=0A
You can solve your = problem by doing this
=0A
 
=0A
THE SQL IS AS FOLLOW= S
  ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59= AND THE TABLE NAME time_interval
=0A
 
=0A
 CO= UNT (*) FROM  
=0A
    (select customer_i= d, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" fr= om time_interval
     where end_ts-start_ts >=3D = '1 hour'
     and '2008-12-07 07:59:59' between star= t_ts and end_ts)
=0A
AS COUNT ;

 
=0A
----= --------------------------------------ORGINAL MESSAGE----------------------= ----------------------------------------
=0A
= =0A
=0AFrom: Ri= chard Huxton <dev@archonet.com>
To: Srikanth <rssrik@yahoo.co.in>
Cc: pgsql-sql@postgresql.org
Sent: Tuesday, 17 March, 2009 18:06:09Subject: Re: [SQL] SQL to= Check whether "AN HOUR PERIOD" is between start and end timestamps

Dear all,
=0A
I have a table that records User Login Sessions wit= h 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. 
=0A
Data from the table (session):
--------= ---------------------
 customer_id | log_session_id  | &n= bsp;        start_ts   &n= bsp;      |      &nb= sp;    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 | 0= 9/12/2008 22:59:02.770218
 1006100888  | 214221233045949 | 27/= 01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182
 1006000008&nb= sp; | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.94= 7509
 1006100825  | 112331229068049 | 12/12/2008 13:17:37.2292= 49 | 12/12/2008 13:56:58.394577
=0A

The requirement is as follows,
=0A
I have to= find out how many User Sessions that were present in any given "1 HOUR TIM= E PERIOD".  A single User Session can span across many days.
Exampl= e:
           &nb= sp; start_ts          | &= nbsp;         end_ts
  = ;  05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218
=0A----= ---------------------------------------------------------------------------= ----------------------
=0A
Let me explain a scenario,
=0A
I have to = find out the number of sessions during the interval '07/01/2009 11:00:00' t= o '07/01/2009 11:59:59'.
=0A

If I have to find the number of session= s present at any instant time say '07/01/2009 11:25:25', i can easily find = out using the following Query,
=0A
select count(log_session_id) from se= ssion where '07/01/2009 11:25:25' between start_ts and end_ts ;
=0ABut,= I have to find the number of sessions present during the "HOUR INTERVAL" '= 07/01/2009 11:00:00' to '07/01/2009 11:59:59'.
=0A
I tried using wildcar= ds in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searchi= ng archives without any success either.
=0A
I feel this is a general req= uirement and this topic should have already been discussed.
=0A
Could so= meone help me solve this please ?  Any lead would do, like some specia= l postgres-function or any other means.
=0A
Many Thanks,

=0A=0A=0A=0A --0-1030704148-1238675473=:93192--