MySql Check if NOW() falls within a weekday/time range
- by Niall
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.
