INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Inverse of UNION results

Inverse of UNION results

(OP)
Hi Folks

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

CODE

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

and

CODE

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


RE: Inverse of UNION results

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

RE: Inverse of UNION results

(OP)
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

RE: Inverse of UNION results

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

CODE --> once

<?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

CODE --> query

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 


RE: Inverse of UNION results

Quote:

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:

CODE

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

RE: Inverse of UNION results

(OP)
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...

CODE

<?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>';
}
?> 

RE: Inverse of UNION results

sorry. the create statement was wrong

CODE

create table seconds (sec INT(2)) 

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

RE: Inverse of UNION results

(OP)
Looking good!

Many thanks indeed both, much appreciated!

Graham

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close