I have a table where there are two columns that contain time_from and time_to of a particular event. Both columns' type is TINYINT(2). For eaxmple
id time_from time_to
__________________________
11 8 14
18 12 17
44 20 24
Some periods overlap. I need to sum-up all time and make sure I do not double-count overlapped time.
Unfortunately I cannot change the column type and must work with what I've got. How can I do this?
The expected result is something like this:
14 - 8 = 6
17 - 12 = 5
24-20 = 4
Overlap is 2 hrs (12 - 14)
Total: 6 + 5 + 4 - 2 = 13
I'm assuming your time_from and time_to columns represent hours in the range 1 to 24.
Edit. As you clarified, I'm assuming 20, 24 covers four hours, that is, 20, 21, 22, 23. Each range excludes the final hour mentioned: [20,24).
You can solve this problem with a sequence table. This is it. (http://sqlfiddle.com/#!9/57cf7f/4/0)
SELECT 1 seq
UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
In MariaDB, it's built in: the table seq_1_to_24 is it.
Join it with your other table like this, and you get one row for each hour in each row of your other table. (http://sqlfiddle.com/#!9/57cf7f/9/0)
SELECT seq.seq, t.*
FROM (
SELECT 1 seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) seq
JOIN times t ON seq.seq >= t.time_from
AND seq.seq < t.time_to
Finally, summarize that with COUNT(DISTINCT seq) hours and you get the number of hours that appear in one or more of the time intervals in your original table. (http://sqlfiddle.com/#!9/57cf7f/10/0)
SELECT COUNT(DISTINCT seq) hours
FROM (
SELECT seq.seq, t.*
FROM (
SELECT 1 seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17
UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) seq
JOIN times t ON seq.seq >= t.time_from
AND seq.seq < t.time_to
) a
Here's what it looks like in MariaDB.
SELECT COUNT(DISTINCT seq) hours
FROM (
SELECT seq.seq
FROM seq_1_to_24 seq
JOIN times t ON seq.seq >= t.time_from
AND seq.seq < t.time_to
) a
The following solution assumes that the time_from and time_to are both sorted ascending and that the overlaps occur only between adjacent rows:
# Without touching the `id` column, I create a sequential column `my_id`, assuming that the name of the table is `times`:
alter table times add my_id int(3) not null after id;
SET @count = 0;
UPDATE times SET my_id = @count:= @count + 1;
# I use a temporary table `times2` that starts with the second row of the `times_from` column; Here, 9999 can be any large enough number:
create temporary table times2 as select my_id, time_from from times limit 1, 9999;
# Now I add a new column `time_end` to the original table. I update it with data from `time_from` column but 'shifted' by one row upwards:
alter table times add time_end int(2) not null;
update times a join times2 b on a.my_id = (b.my_id - 1) set a.time_end = b.time_from;
# Ensure that the value of `time_end` in the last row is not zero:
update times set time_end = time_to order by my_id desc limit 1;
# The required value is taken by using the minimum of columns `time_from` and `time_end`:
select sum(LEAST(time_to,time_end) - time_from) from times;
# Uncomment below to clean up just after getting the results:
# alter table times drop column my_id;
# alter table times drop column time_end;