joacef

I have solved the problem. DROP TABLE IF EXISTS tmp_result; CREATE temporary TABLE IF NOT EXISTS tmp_result ( `starttime` time , `stoptime` time , `debit` varchar(4), `start` time , `stop` time COLLATE utf8_unicode_ci ); INSERT INTO tmp_result SELECT starttime, stoptime, debit, IF ('04:30:00' < starttime, starttime, TIME('04:30:00')) AS start, IF ('19:00:00' > stoptime, stoptime, TIME('19:00:00')) AS stop FROM times HAVING (starttime <= start AND stoptime >= stop AND start < stop) OR (starttime <= stop AND stoptime >= stop AND start < stop) OR (starttime >= start AND stoptime <= stop AND start < stop) ; SELECT *, ROUND(TIME_TO_SEC(TIMEDIFF(stop,start))/3600,2) AS debitcnt FROM tmp_result;

Yes it's correct.

I will try to explain better. If I make an booking for the time 06:30:00-19:00:00 I will run a query on the table times. It will find that between 06:30:00-07:00:00 there will be an debit for 30 minutes with the debitcode ot1 (7, '06:00:00', '07:00:00', 'ot1') It will find that between 07:00:00-16:00 there will be an debit for 9 hours with the debitcode ord (8, '07:00:00', '16:00:00', 'ord') It will find that between 16:00:00-19:00:00 there will be an debit for 3 hours with the debitcode ot1 (9, '16:00:00', '20:00:00', 'ot1')

I'm trying to do a bookingsystem. If I book the time 06:30:00-19:00:00 will it return. 06:30:00-07:00:00 07:00-00-16:00:00 16:00:00-19:00:00 It will also return the hours between the times and debit code. Does anyone have an example? CREATE TABLE IF NOT EXISTS `times` ( `id` int(11) NOT NULL AUTO_INCREMENT, `starttime` time NOT NULL, `stoptime` time NOT NULL, `debit` varchar(4) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ; INSERT INTO `times` (`id`, `starttime`, `stoptime`, `debit`) VALUES (6, '00:00:00', '06:00:00', 'ot2'), (7, '06:00:00', '07:00:00', 'ot1'), (8, '07:00:00', '16:00:00', 'ord'), (9, '16:00:00', '20:00:00', 'ot1'), (10, '20:00:00', '24:00:00', 'ot2');