1

I'm in the process of evaluating the proposed solutions on SO related to the sum of overlapping datetimes in MySQL. I wasn't able to find out a silver-bullet solution, so would like to know if any classic/industrial-grade algorithmic procedure is available or if a custom-made needs to be developed.

Graphical representation

Total should be 8 hours (4+4).

Proposed solution through MySQL

function final_balance($teacher_id, $aa, $teaching_id=0) {
    $dbo = $this->Attendance->getDataSource();
    $years=$this->Attendance->Student->Year->find('list', array('fields' => array('anno', 'data_from')));
    $filteraa='attendances.start>="'.$years[$aa].'"';
    $this->query('SET @interval_id = 0');
    $this->query('SET @interval_end = \'1970-01-01 00:00:00\'');
    $sql='SELECT 
        MIN(start) as start,
        MAX(end) as end 
        FROM (
            SELECT
               @interval_id := IF(attendances.start > @interval_end, @interval_id + 1, @interval_id) AS interval_id,
               @interval_end := IF(attendances.start < @interval_end, GREATEST(@interval_end, attendances.end), attendances.end) AS interval_end,
               attendances.start,
               attendances.end
             FROM attendances
             INNER JOIN attendance_sheets ON (
                 attendance_sheet_id = attendance_sheets.id AND 
                 attendance_sheets.teacher_id='.$teacher_id.' AND '.$filteraa.' AND 
                 attendance_sheet_status_id = 2 AND 
                 attendance_status_id!=3'.
                 ($teaching_id?' AND attendances.teaching_id IN ('.$teaching_id.')':'').'                    
             )
             ORDER BY attendances.start,attendances.end
        ) intervals GROUP BY interval_id';

    // final query to sum in the temp table
    $finalStatement =array(
        'table' => $dbo->expression('('.$sql.')')->value,
        'alias' => 'Attendance',
        'fields' => array(
            'DATE_FORMAT(start, \'%d/%m/%Y\') as data',
            'DATE_FORMAT(start, \'%m-%Y\') as datamese',
            'DATE(start) as datasql',
            $teacher_id.' AS teacher_id',
            'DAY(start) as giorno',
            'MONTH(start) as mese', 
            'YEAR(start) as anno',
            'SEC_TO_TIME(SUM((TIME_TO_SEC(end) - TIME_TO_SEC(start)))) as ore',
        ),
        'conditions' => array(),
        'limit' => null,
        'group' => array('CONCAT(YEAR(start),MONTH(start))', 'DATE(start) WITH ROLLUP'),
        'order' => null
    );
    $finalQuery= $dbo->buildStatement($finalStatement, $this->Attendance);
    return $this->Attendance->query($dbo->expression($finalQuery)->value);
}

References

Sum amount of overlapping datetime ranges in MySQL performs a different task

MySQL: sum time ranges exluding overlapping ones and MySQL: sum datetimes without counting overlapping periods twice both seems to me like not considering all the cases

GeeksForCode: Merge Overlapping Intervals

Community
  • 1
  • 1
Nicola Beghin
  • 464
  • 3
  • 17
  • For anyone interested: in the end I applied http://www.geeksforgeeks.org/merging-intervals/ to MySQL. Updated by question with the code for CakePHP – Nicola Beghin May 02 '17 at 15:29

1 Answers1

0

Depending on the circumstances, the following might be useful and efficient.

Create another table that has one row per hour. Inner join that table with your table while selecting only the new column and dedupe the rows.

You can keep increasing the resolution (eg. to minutes or seconds), but that might make your code run slow.

ElKamina
  • 7,747
  • 28
  • 43
  • Hi ElKamina, as you already stated, it's a solution feasible with slowly-changing data or with few rows. What I'm trying to understand is if there's any broader literature/solution to the issue, as it seems like a common issue to tackle (Cormen's style :D) – Nicola Beghin Jan 29 '17 at 20:17