mysql_generate_rows, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

/*select version() as 'mysql version' ;*/

DROP TABLE IF EXISTS `opening_times` ;

DROP TABLE IF EXISTS `opening_periods` ;

CREATE TABLE `opening_times` 
(
  `id` integer NOT NULL,
  `begin` time NOT NULL,
  `end` time NOT NULL
) ;

CREATE TABLE `opening_periods` 
(
  `id` integer NOT NULL,
  `opening_time_id` integer NOT NULL,
  `begin` date NOT NULL,
  `end` date NOT NULL
) ;

INSERT INTO `opening_times` (`id`, `begin`, `end`) VALUES
(1, '10:00:00', '17:00:00'),
(2, '10:00:00', '18:00:00'),
(3, '10:00:00', '19:00:00');

INSERT INTO `opening_periods` (`id`, `opening_time_id`, `begin`, `end`) VALUES
(1, 3, '2016-03-26', '2016-03-28'),
(2, 2, '2016-03-29', '2016-04-01'),
(3, 1, '2016-04-02', '2016-04-03');

SELECT DATE_ADD( o.`begin`, INTERVAL days day) as date_field, t.begin, t.end
FROM `opening_periods` o INNER JOIN (
SELECT id, rown as days
FROM `opening_periods`,
(SELECT @row := @row + 1 as rown FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(SELECT @row:=-1) x) numbers_table
WHERE rown <= DATEDIFF(`end`,`begin`)) r
ON o.id = r.id
INNER JOIN `opening_times` t ON o.`opening_time_id` = t.id
ORDER BY o.id
;

DROP TABLE `opening_periods` ;
DROP TABLE `opening_times` ;


absolute service time: 0,36 sec