MySQL:入门每天的结束时间戳 [英] MySQL: Get start & end timestamp for each day

问题描述

类似于 MYSQL: Find Start 中的问题和连续计数的结束时间戳，我有一个类似数据的表:

<前>状态时间 |读2014-01-01 00:00 |2552014-01-01 01:00 |2552014-01-01 02:00 |2552014-01-01 03:00 |2552014-01-01 04:00 |2552014-01-01 05:00 |2412014-01-01 06:00 |1892014-01-01 07:00 |1002014-01-01 08:00 |202014-01-01 09:00 |02014-01-01 10:00 |12014-01-01 11:00 |12014-01-01 12:00 |02014-01-01 13:00 |212014-01-01 14:00 |12014-01-01 15:00 |02014-01-01 16:00 |122014-01-01 17:00 |632014-01-01 18:00 |1022014-01-01 19:00 |1982014-01-01 20:00 |2552014-01-01 21:00 |2552014-01-01 22:00 |2552014-01-01 23:00 |2552014-01-02 00:00 |2552014-01-02 01:00 |2552014-01-02 02:00 |2552014-01-02 03:00 |2552014-01-02 04:00 |2552014-01-02 05:00 |2082014-01-02 06:00 |1002014-01-02 07:00 |482014-01-02 08:00 |02014-01-02 09:00 |0

我想提取开始和结束时间戳，其中读数50以上和50以下， 分别.我无法理解另一个答案中提供的 SQL！

预先感谢您的帮助.

解决方案

如果每天只在下方/上方重复一次，您可以使查询变得非常简单；只需找到它下面的最小和最大时间，按日期分组.

SELECT日期(状态时间)状态日期，MIN(CASE WHEN reading<50 THEN statustime ELSE NULL END) start_time,MAX(CASE WHEN reading<50 THEN statustime ELSE NULL END) end_time从我的表GROUP BY 状态日期

用于测试的 SQLfiddle.

Similar to the question at MYSQL: Find Start and End Timestamp of a consecutive count, I have a table with similar data:

StatusTime          | Reading   
2014-01-01 00:00    | 255
2014-01-01 01:00    | 255
2014-01-01 02:00    | 255
2014-01-01 03:00    | 255
2014-01-01 04:00    | 255
2014-01-01 05:00    | 241
2014-01-01 06:00    | 189
2014-01-01 07:00    | 100
2014-01-01 08:00    | 20
2014-01-01 09:00    | 0
2014-01-01 10:00    | 1
2014-01-01 11:00    | 1
2014-01-01 12:00    | 0
2014-01-01 13:00    | 21
2014-01-01 14:00    | 1
2014-01-01 15:00    | 0
2014-01-01 16:00    | 12
2014-01-01 17:00    | 63
2014-01-01 18:00    | 102
2014-01-01 19:00    | 198
2014-01-01 20:00    | 255
2014-01-01 21:00    | 255
2014-01-01 22:00    | 255
2014-01-01 23:00    | 255
2014-01-02 00:00    | 255
2014-01-02 01:00    | 255
2014-01-02 02:00    | 255
2014-01-02 03:00    | 255
2014-01-02 04:00    | 255
2014-01-02 05:00    | 208
2014-01-02 06:00    | 100
2014-01-02 07:00    | 48
2014-01-02 08:00    | 0
2014-01-02 09:00    | 0

I'd like to extract the start and end timestamps where readings go above 50 and below 50, respectively. I just can't get my head around the SQL presented in the other answer!

Thanks in advance for your help.

解决方案

If it only goes below/above again once per day, you can make the query quite simple; just find the min and max time where it's below, grouping by date.

SELECT
  DATE(statustime) statusdate,
  MIN(CASE WHEN reading<50 THEN statustime ELSE NULL END) start_time,
  MAX(CASE WHEN reading<50 THEN statustime ELSE NULL END) end_time
FROM myTable
GROUP BY statusdate

An SQLfiddle to test with.

这篇关于MySQL:入门每天的结束时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！