Not tested, but I think it can help you or at least give you an ideia on how to do it.
select EndDateTime + INTERVAL 1 SECOND as startLazy, (select StartDateTime - INTERVAL 1 SECOND from table t2 where t2.StartDateTime > t1.EndDateTime limit 1) as endLazy from table t1 where (select StartDateTime - INTERVAL 1 SECOND from table t2 where t2.StartDateTime >= t1.EndDateTime limit 1) > (EndDateTime + INTERVAL 1 SECOND) "John Daisley" <mg_s...@hotmail.com> escreveu na mensagem news:m2x571a6edf1004270647j3d1ef220n4eb9394c339f1...@mail.gmail.com... > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
