calculate blocks of free time

	drop table temp_times;
	create table temp_times (id int, user_id int, start_time datetime, end_time datetime);
	insert into temp_times (id, user_id, start_time, end_time) values (1, 1, '2013-01-01 13:45', '2013-01-01 15:50');
	insert into temp_times (id, user_id, start_time, end_time) values  (2, 1, '2013-01-01 10:00', '2013-01-01 13:20');
	insert into temp_times (id, user_id, start_time, end_time) values  (3, 1, '2013-01-01 15:00', '2013-01-01 16:02');
	insert into temp_times (id, user_id, start_time, end_time) values  (4, 1, '2013-01-01 08:10', '2013-01-01 09:10');
	insert into temp_times (id, user_id, start_time, end_time) values  (4, 1, '2013-01-02 09:10', '2013-01-01 11:55');

	insert into temp_times (id, user_id, start_time, end_time) values (1, 2, '2013-01-01 13:45', '2013-01-01 15:50');
	insert into temp_times (id, user_id, start_time, end_time) values  (2, 2, '2013-01-01 10:00', '2013-01-01 13:20');
	insert into temp_times (id, user_id, start_time, end_time) values  (3, 2, '2013-01-01 15:00', '2013-01-01 17:25');
	insert into temp_times (id, user_id, start_time, end_time) values  (4, 2, '2013-01-01 08:10', '2013-01-01 09:10');

	select * from 
	(
		select 
		  t1.user_id, 
		  t1.end_time breakstart,
		  t4.start_time breakend,
		  TIMEDIFF(IFNULL(t4.start_time, STR_TO_DATE(CONCAT(DATE_FORMAT(t1.end_time, '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i')), t1.end_time) breaklength
		from 
		  temp_times t1
		  LEFT JOIN temp_times t2 ON (
		    t2.user_id = t1.user_id and 
		    DATE(t2.start_time) = DATE(t1.start_time) and 
		    t2.start_time <= t1.end_time and 
		    t2.end_time >= t1.end_time and 
		    t1.id <> t2.id
		  )
		  LEFT JOIN temp_times t4 ON (
		      t4.user_id = t1.user_id and 
		      DATE(t4.start_time) = DATE(t1.start_time) and 
		      t4.start_time > t1.end_time
		  )
		  LEFT JOIN temp_times t6 ON (
		      t6.user_id = t1.user_id and 
		      DATE(t6.start_time) = DATE(t6.start_time) and 
		      t6.start_time > t1.end_time
		  )
		where
		  t1.user_id = 1 and 
		  DATE(t1.start_time) = '2013/01/01' and 
		  t2.id IS NULL and 
		  t4.id = (select t5.id from temp_times t5 where t5.user_id = t1.user_id and DATE(t5.start_time) = DATE(t1.start_time) and t5.start_time > t1.end_time order by t5.start_time LIMIT 1) and
		  t6.id = (select t7.id from temp_times t7 where t7.user_id = t1.user_id and DATE(t7.start_time) = DATE(t1.start_time) and t7.start_time > t1.end_time order by t7.end_time LIMIT 1)

		UNION 

		select 
		  1 user_id, 
		  STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 08:00'), '%Y/%m/%d %H:%i') breakstart,
		  IFNULL(
		    (select 
		       MIN(tfirst.start_time)
		     from 
		       temp_times tfirst
		     where 
		        tfirst.user_id = 1 and 
		        DATE(tfirst.start_time) = '2013/01/01'
		     order by tfirst.start_time ASC 
		     LIMIT 1),
		     STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i')
		  ) breakend,

		  (select 
		     TIMEDIFF(
		       IFNULL(tfirst2.start_time, STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 08:00'), '%Y/%m/%d %H:%i')),
		       STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 08:00'), '%Y/%m/%d %H:%i')
		     )
		     from 
		       temp_times tfirst2
		     where 
		        tfirst2.user_id = 1 and 
		        DATE(tfirst2.start_time) = '2013/01/01' and 
		        tfirst2.id = (
						select 
							tearliest.id
						from 
							temp_times tearliest 
						where 
							tearliest.user_id = tfirst2.user_id and 
							DATE(tearliest.start_time) = DATE(tfirst2.start_time)
		                                order by tearliest.start_time LIMIT 1
		        )
		  ) breaklength

		UNION 

		select 
		  1 user_id, 
		  IFNULL(
		    (select 
		       MAX(tlast.end_time)
		     from 
		       temp_times tlast
		     where 
		        tlast.user_id = 1 and 
		        DATE(tlast.start_time) = '2013/01/01'
		     order by tlast.end_time ASC 
		     LIMIT 1),
		     STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i')
		  ) breakstart,
		  STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i') breakend,

		  (select 
		     TIMEDIFF(
		       STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i'),
		       IFNULL(tlast.end_time, STR_TO_DATE(CONCAT(DATE_FORMAT('2013/01/01', '%Y/%m/%d'),' 17:30'), '%Y/%m/%d %H:%i'))
		     )
		     from 
		       temp_times tlast
		     where 
		        tlast.user_id = 1 and 
		        DATE(tlast.start_time) = '2013/01/01' and 
		        tlast.id = (
						select 
							tlatest.id
						from 
							temp_times tlatest 
						where 
							tlatest.user_id = tlast.user_id and 
							DATE(tlatest.start_time) = DATE(tlast.start_time)
		                                order by tlatest.end_time DESC LIMIT 1
		        )
		  ) breaklength
	) total
	having 
	  total.breaklength > 0
	order by 
	  total.breaklength DESC