X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 83154D1B8C8 for ; Thu, 21 Aug 2003 12:10:09 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 44917-04 for ; Thu, 21 Aug 2003 12:09:58 -0300 (ADT) Received: from web1.nexusinternetsolutions.net (web1.nexusinternetsolutions.net [206.47.131.12]) by svr1.postgresql.org (Postfix) with SMTP id 27B93D1BAE7 for ; Thu, 21 Aug 2003 12:09:58 -0300 (ADT) Received: (qmail 32315 invoked from network); 21 Aug 2003 15:09:56 -0000 Received: from unknown (HELO ws1) (65.49.236.97) by web1.nexusinternetsolutions.net with SMTP; 21 Aug 2003 15:09:56 -0000 From: "Dave [Hawk-Systems]" To: Subject: select to detect overlapping timeframes Date: Thu, 21 Aug 2003 11:09:55 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.8 tagged_above=0.0 required=5.0 tests=BAYES_30, FORGED_MUA_OIMO, MSGID_GOOD_EXCHANGE X-Spam-Level: * X-Archive-Number: 200308/93 X-Sequence-Number: 2270 looking for a better way to query and cehck for overlapping timeframes. am selecting records for display that has 3 fields of importance; date_time (unix timestamp) session_time (in seconds) user_id (text field) Of all the sessions recorded, we are looking for a count of duplicate sessions for a given month. currently we are doing this with two seperate calls, but it is taking forever to process; # first query is to gather sessions that took place within a given month $query = "SELECT * FROM logs WHERE name='$username' AND \ date_time>$dategt AND date_time<$datelt \ ORDER BY date_time DESC"; $result = pg_exec($database,$query); $numrows=pg_numrows($result); for($count=0;$count<$numrows;$count++){ $row = pg_fetch_array($result,$count); # as we go through the hits, check for duplicates within that timeframe as well $DUPquery = "SELECT session_id FROM logs WHERE name='$username' AND \ date_time>$sessionstart AND \ (date_time - session_time)<$datetime \ ORDER BY name ASC, date_time DESC"; $DUPresult=pg_exec($database,$DUPquery); $DUPcount=pg_numrows($DUPresult); Obviously this is extremely processor intensive, not to mention it seems sloppy. Any better recommendations, either on altering the above code, or another way to get the information we require? Dave