Inverse of UNION results 2

[maharg]

Hi Folks

I have 2 tables, which show time a particular event was activated, and time that event was cleared.

event_1
id 	activated 	cleared
1 	3 	9
2 	19 	26
3 	35 	46

and

event_2
id 	activated 	cleared
1 	5 	10
2 	17 	22
3 	33 	47
4 	49 	50

I want to find the bands of time that neither event_1 nor event_2 was active.
There could be more than 2 event tables.

I have tried manipulating UNION without success, and wonder if someone could point me in the right direction.

Many thanks

Graham

 

[jpadie]

how are the times represented? are they simply representations of minutes in an hour (i.e. 0-59) or something else?

 

[maharg]

Hi jpadie,

They are just seconds, ultimately I will use unix timestamp.

In the examples above, my expected output is :-

1 to 3
10 to 17
26 to 33
47 to 49
50 onwards

Regards,

Graham

 

[jpadie]

interestingly you are not defining your 'between' criterion inclusively. as id 1 of event_1 is activated at 3, thus I would count 3 out of the result set. likewise 10.

so that changes things a little.

assuming that all your values are in one table (which is by far the most logical table design) this structure would work

<?php
mysql_query('create table seconds (`sec` id(2))');
$sql = 'insert into seconds (sec) values (%d)';
for($i=0;$i<60;$i++) mysql_query(sprintf($sql, $i));
?>

you now have a table of seconds, ranged between 0 and 59.

for a unix timestamp based query you would move to a stored procedure that could take a start and end time as an argument and iterate through the seconds between them

SELECT  sec
FROM    seconds s
WHERE
(
    SELECT  COUNT(*) AS c 
    FROM    events_1 
    WHERE   s.sec > activated 
            AND 
            s.sec < cleared 
) = 0
AND
(
    SELECT  COUNT(*) AS c 
    FROM    events_2 
    WHERE   s.sec > activated 
            AND 
            s.sec < cleared 
) = 0
GROUP BY s.sec ASC

 

[ChrisHunt]

There could be more than 2 event tables

That sounds like a questionable database design - if you add another type of event, you have to create a new table and code more SQL to cope with its appearance.

I'd suggest having a single table like this:

event_type  id   activated   cleared
event_1      1           3         9
event_1      2          19        26
event_1      3          35        46  
event_2      1           5        10
event_2      2          17        22
event_2      3          33        47
event_2      4          49        50

Also, if activated and created are date/timne values, use a date-related datatype for them instead of numbers.

Neither of these points address your actual question, I'm afraid, but getting the design right at the start will save a lot of heartache later on.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[maharg]

Hi jpadie,

Many thanks for your quick response! I have tried the following - slight difference in table names - but no
output evident.

I am currently getting the data from individual tables for event_1 and event_2 as they are legacy and they have a lot of historic data in them I have a couple of test table on the local machine containing the data shown above in my original post.

The time span I am using at present is 0 to 60 seconds.

Not quite sure what I'm doing wrong...

<?php
include ('include_mysql_login_PDO.php');

$begin=0;
$end=60;


// 
mysql_query('create table seconds (`sec` id(2))');
$sql = 'insert into seconds (sec) values (%d)';
for($i=$begin;$i<$end;$i++) mysql_query(sprintf($sql, $i));
//


foreach($db->query('SELECT  sec
FROM    seconds s
WHERE
(
    SELECT  COUNT(*) AS c 
    FROM    event_1 
    WHERE   s.sec > activated 
            AND 
            s.sec < cleared 
) = 0
AND
(
    SELECT  COUNT(*) AS c 
    FROM    event_2 
    WHERE   s.sec > activated 
            AND 
            s.sec < cleared 
) = 0
GROUP BY s.sec ASC ') as $record) 
{
  $sec=$record['sec']; 
  echo $sec.'<br>';
}
?>

 

[jpadie]

sorry. the create statement was wrong

create table seconds (sec INT(2))

remember - only run the create and the insert statements once.

 

[maharg]

Looking good!

Many thanks indeed both, much appreciated!

Graham