Skip to content

Instantly share code, notes, and snippets.

@rootl

rootl/LibraryHoursExceptionsAdmin.sql

Last active March 30, 2016 13:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rootl/7e43de40b11fb502926f to your computer and use it in GitHub Desktop.
Save rootl/7e43de40b11fb502926f to your computer and use it in GitHub Desktop.
Download ZIP
MySQL query to join date ranges with exceptions. Used for Library Hours Webservice with PHP based admin panel. The user will see a date range row listing library hours during that date range, along with any exceptions such as closings, holiday hours, extended hours.
Raw
LibraryHoursExceptionsAdmin.sql
CREATE TABLE IF NOT EXISTS `dayrangehours` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`StartDate` date NOT NULL,
`EndDate` date NOT NULL,
`MonStartT` time DEFAULT NULL,
`MonEndT` time DEFAULT NULL,
`TuesStartT` time DEFAULT NULL,
`TuesEndT` time DEFAULT NULL,
`WedStartT` time DEFAULT NULL,
`WedEndT` time DEFAULT NULL,
`ThursStartT` time DEFAULT NULL,
`ThursEndT` time DEFAULT NULL,
`FriStartT` time DEFAULT NULL,
`FriEndT` time DEFAULT NULL,
`SatStartT` time DEFAULT NULL,
`SatEndT` time DEFAULT NULL,
`SunStartT` time DEFAULT NULL,
`SunEndT` time DEFAULT NULL,
`Term` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
INSERT INTO `dayrangehours` (`id`, `StartDate`, `EndDate`, `MonStartT`, `MonEndT`, `TuesStartT`, `TuesEndT`, `WedStartT`, `WedEndT`, `ThursStartT`, `ThursEndT`, `FriStartT`, `FriEndT`, `SatStartT`, `SatEndT`, `SunStartT`, `SunEndT`, `Term`) VALUES
(5, '2013-08-26', '2013-09-01', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Summer 2013'),
(6, '2013-09-02', '2013-10-15', '07:30:00', '13:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '22:00:00', '10:00:00', '01:00:00', 'Fall 2013'),
(7, '2013-10-16', '2013-11-25', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Fall 2013'),
(8, '2013-11-26', '2013-12-01', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Fall 2013'),
(9, '2013-12-02', '2013-12-10', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Fall 2013'),
(10, '2013-12-11', '2013-12-17', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Fall 2013'),
(11, '2013-12-18', '2014-01-20', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', NULL, NULL, NULL, NULL, 'Winter Intersession 2013-2014'),
(12, '2014-01-21', '2014-03-14', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Spring 2014'),
(13, '2014-03-15', '2014-05-18', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', 'Spring 2014'),
(14, '2014-05-19', '2014-07-06', '08:00:00', '18:00:00', '08:00:00', '18:00:00', '08:00:00', '18:00:00', '08:00:00', '18:00:00', '08:00:00', '16:00:00', '18:00:00', NULL, NULL, NULL, 'Summer Session 1 2014'),
(15, '2014-07-07', '2014-08-08', '08:00:00', '21:00:00', '08:00:00', '21:00:00', '08:00:00', '21:00:00', '08:00:00', '21:00:00', '08:00:00', '16:00:00', NULL, NULL, '14:00:00', '21:00:00', 'Summer Session 2 2014'),
(16, '2014-08-09', '2014-08-24', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', '08:00:00', '16:00:00', NULL, NULL, NULL, NULL, 'Summer Intersession 2014'),
(17, '2014-11-03', '2014-12-12', '08:00:00', '22:01:00', '08:00:00', '01:00:00', '08:00:00', '21:00:00', '08:00:00', '23:00:00', '10:15:00', '01:00:00', NULL, NULL, NULL, NULL, NULL),
(18, '2014-06-30', '2014-07-31', '14:30:00', '12:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(19, '2014-07-02', '2014-07-25', '00:30:00', '19:30:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(20, '2015-01-22', '2015-10-15', '07:30:00', '13:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', NULL, NULL, NULL, NULL, NULL),
(21, '2013-09-02', '2013-10-15', '07:30:00', '13:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:00:00', NULL),
(22, '2014-12-17', '2015-10-15', '07:30:00', '13:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', '10:00:00', '21:00:00', '10:00:00', '01:30:00', NULL),
(23, '2014-11-03', '2014-12-12', '08:00:00', '22:01:00', '08:00:00', '01:00:00', '08:00:00', '21:00:00', '08:00:00', '23:00:00', '10:15:00', '01:00:00', '09:00:00', '20:00:00', '09:00:00', '16:00:00', NULL),
(24, '2015-01-22', '2015-10-15', '07:30:00', '13:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '01:00:00', '07:30:00', '21:00:00', NULL, NULL, NULL, NULL, NULL);
CREATE TABLE IF NOT EXISTS `exceptions` (
`ExceptionID` int(11) NOT NULL AUTO_INCREMENT,
`ExceptionDate` date DEFAULT NULL,
`ExceptionStartT` time DEFAULT NULL,
`ExceptionEndT` time DEFAULT NULL,
`ExceptionClosedDesc` varchar(255) DEFAULT NULL,
`ExceptionSpecialDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ExceptionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ;
INSERT INTO `exceptions` (`ExceptionID`, `ExceptionDate`, `ExceptionStartT`, `ExceptionEndT`, `ExceptionClosedDesc`, `ExceptionSpecialDesc`) VALUES
(2, '2014-08-06', '12:00:00', '01:00:00', 'Test', 'Test'),
(5, '2014-08-12', '12:00:00', '01:00:00', 'Another', 'Test'),
(7, '2013-11-27', '07:30:00', '16:00:00', 'Thanksgiving Break', 'Thanksgiving Break'),
(8, '2013-11-28', NULL, NULL, 'CLOSED - Thanksgiving Day', NULL),
(9, '2013-11-29', NULL, NULL, 'CLOSED - Thanksgiving Break', NULL),
(10, '2013-11-30', NULL, NULL, 'CLOSED - Thanksgiving Break', NULL),
(11, '2013-12-01', '12:00:00', '01:00:00', NULL, 'Thanksgiving Break'),
(12, '2013-12-13', '07:30:00', '01:00:00', NULL, 'Finals Week'),
(13, '2013-12-14', '10:00:00', '01:00:00', NULL, 'Finals Week'),
(14, '2013-12-17', '07:30:00', '22:00:00', NULL, 'Finals Week'),
(15, '2013-12-25', NULL, NULL, 'CLOSED - CHRISTMAS', NULL),
(16, '2014-01-01', NULL, NULL, 'CLOSED - New Years Day', NULL),
(17, '2014-01-20', '12:00:00', '01:00:00', NULL, 'Martin Luther King Holiday'),
(18, '2014-03-15', NULL, NULL, 'CLOSED - Spring Break', NULL),
(19, '2014-03-16', NULL, NULL, 'CLOSED - Spring Break', NULL),
(20, '2014-03-17', '08:00:00', '16:00:00', NULL, 'Spring Break'),
(21, '2014-03-18', '08:00:00', '16:00:00', NULL, 'Spring Break'),
(22, '2014-03-19', '08:00:00', '16:00:00', NULL, 'Spring Break'),
(23, '2014-03-20', '08:00:00', '16:00:00', NULL, 'Spring Break'),
(24, '2014-03-21', '08:00:00', '16:00:00', NULL, 'Spring Break'),
(25, '2014-03-22', NULL, NULL, 'CLOSED - Spring Break', NULL),
(26, '2014-03-23', '12:00:00', '01:00:00', NULL, 'Spring Break'),
(28, '2014-05-10', '10:00:00', '01:00:00', NULL, 'Finals Week'),
(30, '2014-05-14', '07:30:00', '22:00:00', NULL, 'Finals Week'),
(31, '2014-05-15', '08:00:00', '16:00:00', NULL, 'Finals Week'),
(32, '2014-05-16', '08:00:00', '16:00:00', NULL, 'Finals Week'),
(33, '2014-05-17', '09:00:00', '17:00:00', NULL, 'Commencement'),
(34, '2014-05-18', NULL, NULL, 'CLOSED', NULL),
(35, '2014-05-26', NULL, NULL, 'CLOSED - Memorial Day', NULL),
(36, '2014-07-04', NULL, NULL, 'CLOSED - Independence Day', NULL),
(39, '2014-03-12', '07:30:00', '12:00:00', '', 'CLOSED at NOON due to severe weather conditions'),
(40, '2014-08-25', '02:00:00', '21:00:00', 'Test', NULL);
select * from dayrangehours
RIGHT JOIN exceptions
ON (exceptions.ExceptionDate >= dayrangehours.StartDate AND exceptions.ExceptionDate <= dayrangehours.EndDate)
ORDER BY dayrangehours.StartDate DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment