mysql - 两个 Unix_Timestamp 引用的区别

Question

我有一个名为“powerpro”的表，包括一天中每一分钟记录的“power”数据如下:(假设一个月记录包括)

+---------------------+---------------+---------------+---------------+
|      date_time      | phase_1_power | phase_2_power | phase_3_power |
+---------------------+---------------+---------------+---------------+
| 2014/12/01 00:00:00 | 73.0767       | -68.2627      | -73.0767      |
| 2014/12/01 00:01:00 | 73.0293       | -68.3134      | -73.0293      |
| 2014/12/01 00:02:00 | 72.9819       | -68.3641      | -72.9819      |
| 2014/12/01 00:03:00 | 72.9345       | -68.4148      | -72.9345      |
| 2014/12/01 00:04:00 | 72.8871       | -68.4655      | -72.8871      |
| 2014/12/02 00:00:00 | 72.8397       | -68.5162      | -72.8397      |
| 2014/12/02 00:01:00 | 72.7923       | -68.5669      | -72.7923      |
| 2014/12/02 00:02:00 | 72.7449       | -68.6176      | -72.7449      |
| 2014/12/02 00:03:00 | 72.6975       | -68.6683      | -72.6975      |
| 2014/12/02 00:04:00 | 72.6501       | -68.7119      | -72.6501      |
| 2014/12/03 00:00:00 | 72.6027       | -68.7697      | -72.6027      |
| 2014/12/03 00:01:00 | 72.5553       | -68.8204      | -72.5553      |
| 2014/12/03 00:02:00 | 72.5079       | -68.8711      | -72.5079      |
| 2014/12/03 00:03:00 | 72.4605       | -68.9218      | -72.4605      |
| 2014/12/03 00:04:00 | 72.4131       | -68.9725      | -72.4131      |
+---------------------+---------------+---------------+---------------+

我想得到平衡结束时间 UNIX_TIMESTAMP() 值 - 开始时间 UNIX_TIMESTAMP() 值 AND结束时间=开始时间UNIX_TIMESTAMP()值+下一个第7天的UNIX_TIMESTAMP()值

我试过这个:

SELECT UNIX_TIMESTAMP(a1.date_time) AS time_reff, a1.phase_1_power AS ph1,  a1.phase_2_power AS ph2,  a1.phase_3_power AS ph3
FROM powerpro a1

JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY  date_time
    )a2 ON a1.date_time = a2.min

JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY date_time
       ) a3 ON a1.date_time = a3.time - INTERVAL 7 DAY 

WHERE a1.date_time BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() ORDER BY a1.date_time

但只得到空结果。

谁能帮帮我？谢谢

Answer 1

删除 WHERE 部分:它将您的结果集限制为仅那些开始日期在 NOW() 7 天内的条目。同时，由于 JOIN，您只输出间隔 7 天的开始时间 - 结束时间对。结合起来，这意味着唯一可以输出的行是结束时间为 NOW() 且开始时间为 DATE_SUB(NOW(), INTERVAL 7 DAY).

当您删除 WHERE 部分时，您将获得所有间隔 7 天的开始时间 - 结束时间对。

更新 1:

由于这仍然不起作用，让我们尝试逐个重建此查询。

首先试试这个:

SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)

那应该给出每天最早的时间戳。

更新 2:

现在尝试:

SELECT UNIX_TIMESTAMP(a1.date_time) AS time_reff, a1.phase_1_power AS ph1,  a1.phase_2_power AS ph2,  a1.phase_3_power AS ph3
FROM powerpro a1

JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)
    )a2 ON time_reff = a2.min

这应该给出每天最早时间戳的所有阶段。

更新 3:

SELECT UNIX_TIMESTAMP(a1.date_time) AS time_reff, a1.phase_1_power AS ph1,  a1.phase_2_power AS ph2,  a1.phase_3_power AS ph3
FROM powerpro a1

JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)
    )a2 ON UNIX_TIMESTAMP(a1.date_time) = a2.min

JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)
    )a3 ON CAST(a1.date_time AS DATETIME) = DATE_SUB(a3.time, INTERVAL 1 DAY)

更新 4:

SELECT UNIX_TIMESTAMP(a1.date_time) AS time_reff, a1.phase_1_power AS ph1,  a1.phase_2_power AS ph2,  a1.phase_3_power AS ph3
FROM powerpro a1
JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)
    )a2 ON UNIX_TIMESTAMP(a1.date_time) = a2.min
JOIN (SELECT UNIX_TIMESTAMP(date_time) time, MIN(UNIX_TIMESTAMP(date_time)) AS min 
        FROM powerpro
        GROUP BY DATE(date_time)
    )a3 ON UNIX_TIMESTAMP(a1.date_time) = a3.min - 86400