时间戳

首页 > 解决方案 > Mysql query problems (total amount of time)

mysql - Mysql query problems (total amount of time)

问题描述

I’m stuck.. I have the indicators on my heater connected to a raspberry pi. The pi then put the changed state (on and off) with the time into a mysql database. Before I’ve used PHP to present the data but I’m now trying out Grafana. And I cant get the mysql query to present what I want.

I need to know how much time the heater has been activated (0/1) in the specified time range (typically the last 24h). And I need to take into account that the time range may start with ”0”, meaning that the heater has been on the time before that. And the same if it ends with ”1”. And maybe also the percent of the day where it’s been activated.

Can someone please help me? I'm looking for a result like this

+-------+--------------+--------------+

| value | secondsOfDay | PercentOfDay |

+-------+--------------+--------------+

| 0 | 28800 | 33.3 |

| 1 | 57600 | 66.6 |

+-------+--------------+--------------+

I've prepared: http://sqlfiddle.com/#!9/556364a

Thanks!

标签： mysqldatetimegrafana

解决方案

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

注意：根据CURRDATE()示例将失败。你可以。替换CURDATE()为固定值，如2018-11-27;

解释：

  • 考虑到开/关彼此跟随（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

NOW()如果最后一个状态是，则使用计数秒数的示例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

推荐阅读