php - 从 2 个重叠的日期时间范围获取小时数

Question

我想确定如何从 2 个重叠的日期时间范围中获取小时数。例如使用下表:

    CREATE TABLE IF NOT EXISTS `schedules` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `start1` datetime NOT NULL,
      `end1` datetime NOT NULL,
      `start2` datetime NOT NULL,
      `end2` datetime NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37;



    INSERT INTO `schedules` (`id`, `start1`, `end1`, `start2`, `end2`) VALUES
        (1, '2017-04-16T14:00:00', '2017-04-16T23:00:00', '2017-04-16T22:00:00', '2017-04-17T06:00:00'),
        (2, '2017-04-17T17:00:00', '2017-04-18T02:00:00', '2017-04-17T22:00:00', '2017-04-18T06:00:00'),
        (3, '2017-04-18T15:00:00', '2017-04-19T01:00:00', '2017-04-18T22:00:00', '2017-04-19T06:00:00')
        (4, '2017-04-19T22:00:00', '2017-04-20T06:00:00', '2017-04-19T22:00:00', '2017-04-20T06:00:00'),
        (5, '2017-04-20T23:00:00', '2017-04-21T08:00:00', '2017-04-20T22:00:00', '2017-04-21T06:00:00');

结果应该是:

   id, Hours
    1,   1
    2,   4
    3,   3
    4,   8
    5,   7

我以前有过使用多个 IF 语句的方法，但我认为它运行缓慢。如果您能给我一个简单快捷的查询，我将不胜感激。

Answer 1

是的，你说我以前有一个使用多个 IF 语句的方法，这个解决方案看起来像，但无论如何，试试看，可能会更好？

select `schedules`.*, 
CASE 
    WHEN `end2` <=  `start1` OR `start2` >= `end1` THEN  NULL -- not overlaps at all
    WHEN `start2` <= `start1` AND `end2` <= `end1` THEN  TIMESTAMPDIFF(HOUR, `start1`, `end2`)  
    WHEN `start2` <= `start1` AND `end2` > `end1`  THEN  TIMESTAMPDIFF(HOUR, `start1`, `end1`)
    WHEN `start2` > `start1` AND `end2` <= `end1`  THEN  TIMESTAMPDIFF(HOUR, `start2`, `end2`)
    WHEN `start2` > `start1` AND `end2` > `end1`   THEN  TIMESTAMPDIFF(HOUR, `start2`, `end1`)
END AS hourdiff
from `schedules`