sql challenge - using multiple references for blackout dates

Given the 2 tables below, show only the activity times when the product was not offline. Keep in mind that this is a small sample of data. There will be multiple products in the activity table and multiple timestamps in the offline table.

The开发者_运维百科 goal is to output when the product was online. so given the data below, I need a query that will return : rows 3,6,7 - these are the timestamps when the product was online.

mysql> select * from activity;
+------------+---------------------+
| product_id | activity_date       |
+------------+---------------------+
|          1 | 2011-04-13 12:00:00 |
|          1 | 2011-04-13 01:00:00 |
|          1 | 2011-04-13 02:00:00 |
|          1 | 2011-04-13 03:00:00 |
|          1 | 2011-04-13 04:00:00 |
|          1 | 2011-04-13 05:00:00 |
|          1 | 2011-04-13 06:00:00 |
+------------+---------------------+
7 rows in set (0.01 sec)

mysql> select * from offline
+------------+---------------------+---------------------+
| product_id | offline_start       | offline_end         |
+------------+---------------------+---------------------+
|          1 | 2011-04-13 12:00:00 | 2011-04-13 01:00:00 |
|          1 | 2011-04-13 03:00:00 | 2011-04-13 04:00:00 |
+------------+---------------------+---------------------+
2 rows in set (0.01 sec)

create table activity
(
    product_id int,
    activity_date datetime
);

create table [offline]
(
    product_id int,
    offline_start datetime,
    offline_end datetime
);

insert into activity(product_id, activity_date) 
  values (1, '2011-04-13 12:00:00 AM')
        ,(1, '2011-04-13 01:00:00 AM')
        ,(1, '2011-04-13 02:00:00 AM')
        ,(1, '2011-04-13 03:00:00 AM')
        ,(1, '2011-04-13 04:00:00 AM')
        ,(1, '2011-04-13 05:00:00 AM')
        ,(1, '2011-04-13 06:00:00 AM');

insert into [offline](product_id, offline_start, offline_end) 
  values (1, '2011-04-13 12:00:00 AM', '2011-04-13 01:00:00 AM')
        ,(1, '2011-04-13 03:00:00 AM', '2011-04-13 04:00:00 AM');        

select  a.*
from    activity a
        left join offline o
            on a.product_id = o.product_id
            and a.activity_date between o.offline_start and o.offline_end
where   o.product_id IS NULL

Don't have a mysql server setup at home to test it but if the AM doesnt work, then 12 AM is 00:00:00 so the first entry on the activity table should be

1, '2011-04-13 00:00:00'

and the first entry for the offline table should be

1, '2011-04-13 00:00:00', '2011-04-13 01:00:00'

tested this on my sql server and here is the output:

product_id  activity_date
1   2011-04-13 02:00:00.000
1   2011-04-13 05:00:00.000
1   2011-04-13 06:00:00.000