Booking sytem query for debit

[joacef]

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');}

[awjudd]

You are going to need to give a fair bit more information because I'm not sure what you are getting at ...

 

What is the issue you are trying to resolve?

 

~awjudd

[joacef]

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')

 

 

[fenway]

You simply want to find all time ranges that include a portion of your range?

[joacef]

You simply want to find all time ranges that include a portion of your range?

 

Yes it's correct.

[fenway]

WHERE $startTime >= starttime OR $endTime <= stoptime

[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;}