pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave [Hawk-Systems] <dave@hawk-systems.com>
To: pgsql-php@postgresql.org
Subject: select to detect overlapping timeframes
Date: Thu, 21 Aug 2003 11:09:55 -0400
Message-ID: <DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com> (raw)

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





view thread (2+ messages)  latest in thread

Message-ID: <DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com>
Permalink:  ../DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com/
Also on:    postgresql.org/message-id/DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: dave@hawk-systems.com
  Subject: Re: select to detect overlapping timeframes
  In-Reply-To: <DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com>
  References: <DBEIKNMKGOBGNDHAAKGNKEFLDLAC.dave@hawk-systems.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox