On 9/1/2011 09:42, Brandon Phelps wrote:
On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > ...
> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
00:00:00')
> > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30
12:36:53')
> In that case your logic here simplifies to:
> WHERE
> open_dt >= '2011-08-30 00:00:00'
> AND
> close_dt <= '2011-08-30 12:36:53'
> Now add an index over open_dt and close_dt and see what happens.
> Jochem
Jochem,
I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull 1
days worth of connections, I would miss that entry. Basically I want to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start and
end dates, I need to see that record.
Any other ideas?
I believe Jochem was on the right track but he got his dates reversed.
Let's try a little ASCII art to show the situation. I will setup a query
window with two markers (s) and (e). Events will be marked by |----|
markers showing their durations.
a) (s) (e)
b) |---|
c) |---|
d) |---|
e) |--------------------|
f) |---|
g) |---|
To describe these situations:
a) is the window for which you want to query (s) is the starting time
and (e) is the ending time for the date range you are interested in.
b) the event starts and stops before your window exists. It won't be
part of your results.
c) the event starts before the window but ends within the window -
include this
d) the event starts and ends within the window - include this
e) the event starts before the window and ends after the window -
include this
f) the event starts inside the window but ends beyond the window -
include this.
g) the event starts and ends beyond the window - exclude this.
In order to get every event in the range of c-f, here is what you need
for a WHERE clause
WHERE start <= (ending time) and end >= (starting time)
Try that and let us know the results.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
