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.