Mysql查询问题(总时间)

Question

我卡住了..。我的加热器上的指示灯与覆盆子圆周率相连。然后pi将随时间变化的状态(打开和关闭)放到mysql数据库中。在我使用PHP表示数据之前，我现在正在尝试Grafana。而且我无法让mysql查询显示我想要的内容。

我需要知道在指定的时间范围内(通常是最后的24小时)加热器被激活的时间(0/1)。我需要考虑的是，时间范围可能从“0”开始，这意味着加热器在此之前的时间。如果它以“1”结尾的话也是一样的。也可能是一天中激活的百分比。

有人能帮帮我吗？我在找这样的结果

+

( secondsOfDay ) PercentOfDay _x

+

\x{e76f}\x{e76f} 28800 \x{e76f} 33.3 \x{e76f}

\x{e76f}\x{e76f}1\x{e76f} 57600 \x{e76f} 66.6 \x{e76f}\x{e76f}

+

我准备好了：http://sqlfiddle.com/#!9/556364a

谢谢!

Answer 1

要获取所需的数据，可以使用以下查询：

注意：取决于CURRDATE()，示例将失败。你可以去ofc。将CURDATE()替换为固定值(如2018-11-27)；

解释：

• 该查询是在考虑到on/off相互跟随的情况下，将表本身连接起来(L.id = R.id -1)。
• 查询选择任何结果，其中"on“或"off”是today。
• 如果on是昨天的话，那么“准时”就被“更正”为“今日的00:00:00：case when L.Time < CURDATE() then CURDATE() else L.Time end as onTime”。
• 如果最后一个条目是on，则关闭时间将被“更正”为“明天的00:00:00：COALESCE(R.time, CURDATE() + Interval 1 day)”(注意:您可能希望使用NOW()而不是CURDATE() + Interval 1 day，以使当前的秒数一直保持到“现在”)。
• 使用相同的两种方法计算运行秒数。

查询：

SELECT 
  L.playtime_id AS LID,
  R.playtime_id AS RID,
  case when L.Time < CURDATE() then CURDATE() else L.Time end as onTime,
  COALESCE(R.time, CURDATE() + Interval 1 day) AS offTime,
  (  
      UNIX_TIMESTAMP(COALESCE(R.time, CURDATE() + Interval 1 day)) -  
      UNIX_TIMESTAMP(case when L.Time < CURDATE() then CURDATE() else L.Time end)
  ) as RunningSeconds
FROM item0005 as L
LEFT JOIN item0005 AS R 
ON L.playtime_id = R.playtime_id -1

WHERE
  L.`value` = 1 AND
  (
    DATE(L.Time) = CURDATE() OR
    DATE (R.Time) = CURDATE()
  )
;

结果示例：

 LID    RID     onTime                  offTime                 RunningSeconds
 2      3       2018-11-27T09:00:00Z    2018-11-27T11:26:24Z    8784
 4      5       2018-11-27T11:26:27Z    2018-11-27T11:28:29Z    122
 6      7       2018-11-27T11:29:39Z    2018-11-27T11:39:55Z    616
 8      (null)  2018-11-27T11:50:55Z    2018-11-28T00:00:00Z    43745

假设明天00:00:00的例子：http://sqlfiddle.com/#!9/20bc14/1

如果最后一个状态为on：http://sqlfiddle.com/#!9/3c6227/1，则使用on：http://sqlfiddle.com/#!9/3c6227/1来计数秒数

如果您只需要这些集合，您可以使用另一个环境选择并通过知道一天有86400秒的时间来计算百分比：

SELECT
  SUM(RunningSeconds) AS RunningSeconds,
  SUM(RunningSeconds) / 86400 * 100 AS PercentageRunning
 FROM (
   ...
 ) as temp;

http://sqlfiddle.com/#!9/20bc14/5

Answer 2

尝尝这个

    SELECT VALUE, SUM(TIME_TO_SEC(TIMEDIFF(T2,T1))), 100*SUM(TIME_TO_SEC(TIMEDIFF(T2,T1)))/86400 FROM
(SELECT A.VALUE AS VALUE, COALESCE(B.TIME, TIMESTAMP(CURDATE()-2)) AS T1, A.TIME AS T2
FROM ITEM0005 A
LEFT JOIN ITEM0005 B
ON A.PLAYTIME_ID-1= B.PLAYTIME_ID
WHERE A.VALUE='0'
UNION
SELECT '1' AS VALUE, A.TIME AS T1, COALESCE(B.TIME, TIMESTAMP(CURDATE()-1)) AS T2
FROM ITEM0005 A
LEFT JOIN ITEM0005 B
ON A.PLAYTIME_ID= B.PLAYTIME_ID-1
WHERE A.VALUE='0') C
GROUP BY VALUE;