Skip to content

Instantly share code, notes, and snippets.

@carpii
Created December 6, 2013 16:20
Show Gist options
  • Save carpii/7827530 to your computer and use it in GitHub Desktop.
Save carpii/7827530 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment