MySQL从日期时间计算ON-OFF时间

Question

我有一个简单的架构

CREATE TABLE Stack (
  `id` INTEGER,
  `datetime` DATETIME,
  `status` VARCHAR(3)
);

INSERT INTO Stack
  (`id`, `datetime`, `status`)
VALUES
  ('1', '2020-01-01 10:00:00', 'ON'),
  ('2', '2020-01-01 11:00:00', 'ON'),
  ('3', '2020-01-01 12:00:00', 'OFF'),
  ('4', '2020-01-01 12:30:00', 'OFF'),
  ('5', '2020-01-01 15:00:00', 'ON'),
  ('6', '2020-01-01 16:00:00', 'ON'),
  ('7', '2020-01-01 17:30:00', 'OFF'),
  ('8', '2020-01-01 18:00:00', 'ON');

实际上是一种设备。该设备向我发送日期时间和机器状态。我需要以 00:00:00 等格式获取/计算机器的开关时间，检查数据库的每一行。
为了简化，我制作了一个 DB Fiddle:
https://www.db-fiddle.com/f/dpypuY7qc8apmJfC3qrovf/2
有什么帮助吗？谢谢

Answer 1

除了从“ON”到“NOW()”的最后一位之外，我得到了一个基本的持续时间。它看起来像这样:

SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime))) AS duration
FROM Stack AS S1,
     Stack AS S2
WHERE S1.id + 1 = S2.id AND
      S1.status = 'ON'

这可能也可以写为 JOIN，大多数人更喜欢这样，但这相当于。正如你所看到的，有点与时俱进。结果是:

06:30:00

我在您的数据库中发现了一个错误，有两个 ID 的值为 7。我将第二个更改为 8，现在结果是:

04:30:00

那不可能是对的。哈哈……对不起，让我想想。好的，检查了一下，是正确的。 06:30:00 是由数据库错误引起的。
下一个查询将计算数据库表末尾的剩余时间:

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, `datetime`, NOW())) AS `duration`
FROM `Stack` 
WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
      `status` = 'ON'

它现在返回荒谬的:

838:59:59

这不是正确的结果，所以忽略它。日期对于此查询来说太早了。为了从数据库中获得合理的结果，我更改了 2020-01-01 中的所有日期。至 2021-01-22 .
最后我们需要将这两者结合起来。这相对简单:

SELECT
  SEC_TO_TIME(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime)) AS duration
     FROM Stack AS S1,
          Stack AS S2
     WHERE S1.id + 1 = S2.id AND
           S1.status = 'ON')
     +
     (SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
      FROM `Stack` 
      WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
            `status` = 'ON')
   );

那应该这样做。现在我确信一定有更好的方法来做到这一点，但是，嘿，它有效!
哦，如果最后一个状态为“OFF”，则结果为 NULL。让我来做这件事。这应该做一些事情:

SELECT
  SEC_TO_TIME(CAST(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, `S1`.`datetime`, `S2`.`datetime`)) AS duration
     FROM Stack AS `S1`,
          Stack AS `S2`
     WHERE `S1`.`id` + 1 = `S2`.`id` AND
           `S1`.`status` LIKE 'ON')
     +
     IFNULL((SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
             FROM `Stack` 
             WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
                   `status` LIKE 'ON'), 0)
   AS UNSIGNED));

我添加了 CAST(.... AS UNSIGNED)在第二个之后删除任何东西。