MySql Check if NOW() falls within a weekday/time range

Question

I have a table as follows:

CREATE TABLE `zonetimes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `zone_id` int(10) unsigned NOT NULL,
  `active_from_day` tinyint(1) unsigned NOT NULL DEFAULT '2',
  `active_to_day` tinyint(1) unsigned NOT NULL DEFAULT '2',
  `active_from` time NOT NULL,
  `active_to` time NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

So, a user could add a time entry starting on a particular day and time and ending on a particular day and time, eg: Between Monday 08:00 and Friday 18:00 or Between Thursday 15:00 and Tuesday 15:00 (Note the crossover at the end of the week).

I need to query this data and determine if a zone is currently active (NOW(), DAYOFWEEK() etc)... This is turning out to be quite tricky.

If I didn't have overlaps, eg: from 'Wednesday 8pm to Tuesday 4am' or from 'Thursday 4pm to Tuesday 4pm' this would be easy with BETWEEN.

Also, need to allow a user to add for the entire week, eg: Monday 8am - Monday 8am (This should be easy enough, eg: where (active_from_day=active_to_day AND active_from=active_to) OR ..

Any ideas?

Note: I found a similar question here Timespan - Check for weekday and time of day in mysql but it didn't get an answer. One of the suggestions was to store each day as a separate row. I would much rather store one time span for multiple days though.

Update

Terje D's query below works perfectly. Some test scenarios:

+---------------------+-----------------+---------------+-------------+-----------+------------+
| NOW( )              | active_from_day | active_to_day | active_from | active_to | active_now |
+---------------------+-----------------+---------------+-------------+-----------+------------+
| 2012-10-31 23:41:55 |               1 |             4 | 08:00:00    | 23:40:00  |          0 |
| 2012-10-31 23:42:25 |               1 |             4 | 08:00:00    | 23:45:00  |          1 |
| 2012-10-31 23:42:57 |               4 |             1 | 08:00:00    | 09:45:00  |          1 |
| 2012-10-31 23:43:36 |               4 |             4 | 23:00:00    | 09:45:00  |          1 |
| 2012-10-31 23:44:10 |               5 |             4 | 00:00:00    | 23:44:00  |          0 |
| 2012-10-31 23:44:27 |               5 |             4 | 00:00:00    | 23:45:00  |          1 |
| 2012-10-31 23:45:14 |               2 |             2 | 00:00:00    | 00:00:00  |          0 |

Results above were generated by running Terje's query a few times:

SELECT NOW( ) , active_from_day, active_to_day, active_from, active_to, (
DAYOFWEEK( NOW( ) ) > active_from_day
OR DAYOFWEEK( NOW( ) ) = active_from_day
AND TIME( NOW( ) ) > active_from
)
AND (
DAYOFWEEK( NOW( ) ) < active_to_day
OR DAYOFWEEK( NOW( ) ) = active_to_day
AND TIME( NOW( ) ) < active_to
)
OR (
active_from_day > active_to_day
OR active_from_day = active_to_day
AND active_from > active_to
)
AND (
(
DAYOFWEEK( NOW( ) ) > active_from_day
OR DAYOFWEEK( NOW( ) ) = active_from_day
AND TIME( NOW( ) ) > active_from
)
OR (
DAYOFWEEK( NOW( ) ) < active_to_day
OR DAYOFWEEK( NOW( ) ) = active_to_day
AND TIME( NOW( ) ) < active_to
)
) AS active_now FROM zonetimes

Answer 1

Scrap this and see Terje D's answer.

One possible method (which results in a messy query) using Skpd's idea:

I created a start day (tinyint 0 to 6 for Monday to Sunday) and then a start time (int minutes from midnight) and a range (int minutes). So the period for which a zone is active is between (day_of_week+start) AND (day_of_week+start + range) When queryig I query last week's period and also this week's period to catch both. I'm quite sure there is a more elegant approach, but I think it works for all combinations. and keeps storage to a minimum

CREATE TABLE `azonetimes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `zone_id` int(10) unsigned NOT NULL,
  `day_of_week` tinyint(1) unsigned NOT NULL DEFAULT '2',
  `start` int(11) unsigned NOT NULL DEFAULT '0',
  `range` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

Query: (For Wednesday 07:50am)

    SELECT 
        azonetimes.*, (
            NOW() 
            #Check for last week
            BETWEEN     
                        (FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(CURDATE() - INTERVAL WEEKDAY( CURDATE() ) - CAST(azonetimes.day_of_week AS SIGNED INT) +
                        IF( WEEKDAY( CURDATE() ) >  CAST(azonetimes.day_of_week AS SIGNED INT), 0, 7 ) - 7 DAY )) + `start`*60) )
                    AND (FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(CURDATE() - INTERVAL WEEKDAY( CURDATE() ) - CAST(azonetimes.day_of_week AS SIGNED INT) +
                        IF( WEEKDAY( CURDATE() ) >  CAST(azonetimes.day_of_week AS SIGNED INT), 0, 7 ) - 7 DAY )) + ((`range` + `start`)*60)))
            )
        OR 
        (
            NOW() 
            #Check for this week
            BETWEEN     
                        (FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(CURDATE() - INTERVAL WEEKDAY( CURDATE() ) - CAST(azonetimes.day_of_week AS SIGNED INT) +
                        IF( WEEKDAY( CURDATE() ) >  CAST(azonetimes.day_of_week AS SIGNED INT), 0, 7 )  DAY )) + `start`*60) )
                    AND (FROM_UNIXTIME(UNIX_TIMESTAMP(DATE(CURDATE() - INTERVAL WEEKDAY( CURDATE() ) - CAST(azonetimes.day_of_week AS SIGNED INT) +
                        IF( WEEKDAY( CURDATE() ) >  CAST(azonetimes.day_of_week AS SIGNED INT), 0, 7 )  DAY )) + ((`range` + `start`)*60)))
            )
        AS open
        FROM azonetimes


+----+---------+-------------+-------+-------+------+
| id | zone_id | day_of_week | start | range | open |
+----+---------+-------------+-------+-------+------+
|  1 |       1 |           2 |   300 |   900 |    1 |   << Wed 5am to 8pm
|  2 |       0 |           1 |     0 |  2400 |    1 |   << Tue 00:00 to Wed 4pm (40 hrs)
|  3 |       4 |           4 |   300 |   900 |    0 |   << Fri 5am to 8pm
|  4 |       0 |           5 |   900 |  7200 |    1 |   << Sat 3pm to Thu 3pm
+----+---------+-------------+-------+-------+------+

Answer 2

This logic should worK:

IF active_to >= active_from AND NOW() BETWEEN active_from AND active_to
OR active_to < active_from AND NOW NOT BETWEEN active_to AND active_from

i.e.

IF (DAYOFWEEK(NOW()) > active_from_day 
   OR DAYOFWEEK(NOW()) = active_from_day AND TIME(NOW()) > active_from)
AND (DAYOFWEEK(NOW()) < active_to_day
   OR DAYOFWEEK(NOW()) = active_to_day AND TIME(NOW()) < active_to)
OR (active_from_day > active_to_day 
   OR active_from_day = active_to_day AND active_from > active_to)
AND ((DAYOFWEEK(NOW()) > active_from_day 
   OR DAYOFWEEK(NOW()) = active_from_day AND TIME(NOW()) > active_from)
OR (DAYOFWEEK(NOW()) < active_to_day
   OR DAYOFWEEK(NOW()) = active_to_day AND TIME(NOW()) < active_to))

(The zone is active if the time is both after start and before end, or if start > end and the time is either after start or before end)