Query to get unique time ranges in MySQL. Next up: see if this can be refactored

gistfile1.txt

mysql> select * from intervals;
+----+----------+----------+
| id | start    | end      |
+----+----------+----------+
|  1 | 15:30:00 | 16:20:00 |
|  2 | 10:00:00 | 13:00:00 |
|  3 | 15:00:00 | 16:09:00 |
|  4 | 11:00:00 | 14:00:00 |
|  5 | 16:20:00 | 16:30:00 |
|  6 | 09:00:00 | 14:00:00 |
+----+----------+----------+

gistfile2.sql

SELECT CASE
        WHEN (@previous_end IS NULL OR @previous_end < intervals.start)
        THEN @time_group := @time_group + 1
        ELSE @time_group
      END AS time_group,
      @previous_start := intervals.start AS start,
      @previous_end := intervals.end AS end
FROM (
  SELECT @previous_start := NULL,
        @previous_end := NULL,
        @time_group := 0,
        intervals.start,
        intervals.end
  FROM intervals
  ORDER BY intervals.start, intervals.end
) AS intervals
ORDER BY intervals.start, intervals.end

gistfile3.txt

+------------+----------+----------+
| time_group | start    | end      |
+------------+----------+----------+
|          1 | 09:00:00 | 14:00:00 |
|          1 | 10:00:00 | 13:00:00 |
|          1 | 11:00:00 | 14:00:00 |
|          2 | 15:00:00 | 16:09:00 |
|          2 | 15:30:00 | 16:20:00 |
|          2 | 16:20:00 | 16:30:00 |
+------------+----------+----------+

gistfile4.sql

SELECT MIN(intervals.start) AS start,
      MAX(intervals.end) AS end
FROM (
  SELECT CASE
          WHEN (@previous_end IS NULL OR @previous_end < intervals.start)
          THEN @time_group := @time_group + 1
          ELSE @time_group
        END AS time_group,
        @previous_start := intervals.start AS start,
        @previous_end := intervals.end AS end
  FROM (
    SELECT @previous_start := NULL,
          @previous_end := NULL,
          @time_group := 0,
          intervals.start,
          intervals.end
    FROM intervals
    ORDER BY intervals.start, intervals.end
  ) AS intervals
  ORDER BY intervals.start, intervals.end
) intervals
GROUP BY intervals.time_group

gistfile5.txt

+----------+----------+
| start    | end      |
+----------+----------+
| 09:00:00 | 14:00:00 |
| 15:00:00 | 16:30:00 |
+----------+----------+

time_registrations with date, grouped per month.sql

SELECT YEAR(time_registrations.date) AS year,
      MONTH(time_registrations.date) AS month,
      SUM(TIME_TO_SEC(TIMEDIFF(time_registrations.endtime, time_registrations.begintime)) / 60) AS minutes
FROM (
  SELECT time_registrations.date,
        MIN(time_registrations.begintime) AS begintime,
        MAX(time_registrations.endtime) AS endtime
  FROM (
    SELECT CASE
            WHEN (@previous_date IS NULL OR @previous_date <> time_registrations.date)
                  OR (@previous_endtime IS NULL OR @previous_endtime < time_registrations.begintime)
            THEN @time_group := @time_group + 1
            ELSE @time_group
          END AS time_group,
          @previous_date := time_registrations.date AS date,
          @previous_begindate := time_registrations.begintime AS begintime,
          @previous_endtime := time_registrations.endtime AS endtime
    FROM (
      SELECT @previous_date := NULL,
            @previous_begindate := NULL,
            @previous_endtime := NULL,
            @time_group := 0,
            time_registrations.date,
            time_registrations.begintime,
            time_registrations.endtime
      FROM time_registrations
      ORDER BY time_registrations.date, time_registrations.begintime, time_registrations.endtime
    ) AS time_registrations
    ORDER BY time_registrations.date, time_registrations.begintime, time_registrations.endtime
  ) time_registrations
  GROUP BY time_registrations.time_group
) time_registrations
GROUP BY YEAR(time_registrations.date), MONTH(time_registrations.date);