Hmm. You seem to have overlap, too. I suspect this would be easiest to do in code - the data you're looking for doesn't exist in the data you have, only the opposite of that data does.
You could try populating a table with a full day, using the resolution you need (1 minute resolution means 1440 records) and then (somehow) join with your data table and use "not between"; but they you'd get a list of free $resolution blocks, which you may still want to aggregate into from-to blocks using code. On Tue, Apr 27, 2010 at 3:47 PM, John Daisley <mg_s...@hotmail.com> wrote: > 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. > > Regards > > > > -- > John Daisley > > Certified MySQL 5 Database Administrator > Certified MySQL 5 Developer > Cognos BI Developer > > Telephone: +44 (0)7918 621621 > Email: john.dais...@butterflysystems.co.uk > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
