php - 从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

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

最佳答案

是的，你说的是I previously got a way using multiple IF statement，这个解决方案看起来很像，但不管怎样，只要试试，可能会更好？

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`

关于php - 从2个重叠的日期时间范围中获取小时数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43950079/