pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select to detect overlapping timeframes
2+ messages / 2 participants
[nested] [flat]

* select to detect overlapping timeframes
@ 2003-08-21 15:09 Dave [Hawk-Systems] <dave@hawk-systems.com>
  2003-08-21 19:50 ` Re: select to detect overlapping timeframes DeJuan Jackson <djackson@speedfc.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Dave [Hawk-Systems] @ 2003-08-21 15:09 UTC (permalink / raw)
  To: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: select to detect overlapping timeframes
  2003-08-21 15:09 select to detect overlapping timeframes Dave [Hawk-Systems] <dave@hawk-systems.com>
@ 2003-08-21 19:50 ` DeJuan Jackson <djackson@speedfc.com>
  0 siblings, 0 replies; 2+ messages in thread

From: DeJuan Jackson @ 2003-08-21 19:50 UTC (permalink / raw)
  To: Dave [Hawk-Systems] <dave@hawk-systems.com>; +Cc: pgsql-php

(NOTE: I don't use outlook [Mozilla on a Linux box], but I'm gonna top 
post and fully quote)

I'm not certain what you want but this might be an alternative to what 
you are trying to do (It might not perform better).

SELECT DISTINCT t1.*
FROM log t1, log t2
WHERE t1.date_time BETWEEN $dategt AND $datelt
     AND t2.date_time BETWEEN $dategt AND $datelt
     AND t1.name = '$username'
     AND t2.name = '$username'
     AND t1.date_time BETWEEN t2.date_time AND t2.date_time + 
t2.session_time
ORDER BY t1.name ASC /* I don't see why you need to do this since you 
are looking at one name anyway */, t1.date_time DESC

Tweak as needed for indexes and such.

name='$username' AND \
	date_time>$dategt AND date_time<$datelt 



Dave [Hawk-Systems] wrote:

>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
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>  
>





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-08-21 19:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-21 15:09 select to detect overlapping timeframes Dave [Hawk-Systems] <dave@hawk-systems.com>
2003-08-21 19:50 ` DeJuan Jackson <djackson@speedfc.com>

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