Query Help!

--0016e6d785456551920485382279
Content-Type: text/plain; charset=ISO-8859-1

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.daisley@butterflysystems.co.uk

--0016e6d785456551920485382279--

Re: Query Help!

--000e0cd2e04ecc0a5d04853842ee
Content-Type: text/plain; charset=ISO-8859-1

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 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.daisley@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

--000e0cd2e04ecc0a5d04853842ee--

Re: Query Help!

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" escreveu na mensagem
news:m2x571a6edf1004270647j3d1ef220n4eb9394c339f1949@mail.gm ail.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.daisley@butterflysystems.co.uk
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org