总结所有时间段

Question

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

Answer 1

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