List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:April 27 2010 1:47pm
Subject:Query Help!
View as plain text  
Hi All,

I have a query I need to run but can't think how to get this working so I am
hoping someone can advise.

I have a table which logs start and end times of Scheduled jobs. It includes
for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both
`StartDateTime` and `EndDateTime` are 'datetime' datatypes.

What I need to do is find all times in a day when there was nothing running
on the system - so all times which do not occur between any of the
`StartDateTime` and `EndDateTime` values for a particular day.

A simple example, if the table had values

*DayId     StartDateTime                EndDateTime
1            2010-02-26 16:40:27       2010-02-26 16:41:27
1            2010-02-26 16:41:21       2010-02-26 16:45:57
1            2010-02-26 16:47:01       2010-02-26 16:49:21
1            2010-02-26 16:49:27       2010-02-26 16:49:55

*I can see the system was free between 16:45:57 and 16:47:01 on 26th
February 2010 and this is what I would need the query to return only working
with a lot more data. Any ideas?

Thank you in advance for any help, suggestions. This is currently on a MySQL
5.1 system.


John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@stripped

Query Help!John Daisley27 Apr
  • Re: Query Help!Johan De Meersman27 Apr
  • Re: Query Help!joao27 Apr