每小時、每天或每月的總降水量表

我將雨量計的數據儲存在 mySQL 中，需要幫助來列出數據。我每 5 分鐘保存一次數據，並以時間戳的形式使用保存日期。因此，在每個小時結束時，HH: 55 將始終是我需要在 DD-MM-YYYY 當天列出的最大小時總數

是否可以在第二個 SELECT 中列出以這種方式儲存的每日或每月數據總數？如果是這樣，怎麼做？非常感謝你。

編輯：Mysql 版本：5.7.32-35-log 我每 5 分鐘保存一次數據，但在 HH:55 結束時，每小時總降水量總是最高的。

所以我有：

id | rain | timestamp
1 | 0.0 | 2021-10-04 12:00:00
2 | 0.0 | 2021-10-04 12:05:00
3 | 0.6 | 2021-10-04 12:10:00
4 | 0.9 | 2021-10-04 12:15:00
5 | 0.9 | 2021-10-04 12:20:00
6 | 1.4 | 2021-10-04 12:25:00
........
12 | 2.5 | 2021-10-04 12:55:00 // MAX rain

13 | 0.0 | 2021-10-04 13:00:00 // new hour and clean rain to 0.0
14 | 0.0 | 2021-10-04 13:05:00
15 | 0.6 | 2021-10-04 13:10:00

我建議你不要每小時選擇一個時間點，而是選擇每小時的最大降雨量

SELECT date(ts), hour(ts), max(rain)
FROM t
GROUP BY date(ts), hour(ts)

我認為你的方法是有缺陷的。如果您正在測量降水量，那麼您希望從一小時開始到該小時結束（即從 13:00:00.000000001 到 14:00:00.000000000）而不是從一個ns 在 13:55 到 14:05 之後。

您的方法基本上錯過了整點 55 分鐘到小時標記之間的 5 分鐘降水！

這可以通過一個例子來最好地展示（下面的程式碼在小提琴上可用）：

CREATE TABLE prec -- precipitation and NOT rain! My dad was a meterologist
(
 id INT NOT NULL,
 rain FLOAT NOT NULL,
 ts   TIMESTAMP NOT NULL
);

填充它：

現在，我認為您以錯誤的方式處理此問題！

您想要的更像是 13:00:00 和 13:05:00 之間的降水，然後是 13:05:00 和 13:10:00 之間的降水。

因此，我們假設降水（就在 13:00:00 之後**）**為零，但13:00:00 的測量值表示它介於 12:00:00 和 13:00:00 之間，所以我會有更多類似的東西這：

基本上，任何時期的第一次測量都是在整點後 5 分鐘而不是整點！

INSERT INTO prec VALUES

(0, 3.5, '2021-10-04 11:55:00'),
(1, 4.2, '2021-10-04 12:00:00'),  -- last measurement - highest

--
-- starts again after the hour
--


(2 ,  0.2, '2021-10-04 12:05:00'),  -- first measurement for period
(3 ,  0.6, '2021-10-04 12:10:00'),
(4 ,  0.9, '2021-10-04 12:15:00'),
(5 ,  0.9, '2021-10-04 12:20:00'),
(6 ,  1.4, '2021-10-04 12:25:00'),
(12,  2.5, '2021-10-04 12:55:00'),

(13, 2.6, '2021-10-04 13:00:00'),    -- last measurement for period


--
-- starts again **FROM** the hour!
--

(14,  0.1, '2021-10-04 13:05:00'),  -- 05:00 - lowest
(15,  0.6, '2021-10-04 13:10:00'),
(24,  1.5, '2021-10-04 13:55:00'),
(25,  1.8, '2021-10-04 14:00:00');  -- 00:00 - highest

查詢：

查詢 1（有缺陷）

SELECT 
 rain, ts, DATE(ts), HOUR(ts), MINUTE(ts)
FROM
 prec
WHERE MINUTE(ts) = 55;

結果：

rain    ts  DATE(ts)    HOUR(ts)    MINUTE(ts)
3.5     2021-10-04 11:55:00     2021-10-04  11  55
2.5     2021-10-04 12:55:00     2021-10-04  12  55
1.5     2021-10-04 13:55:00     2021-10-04  13  55

查詢2（正確方法）

SELECT
 rain, ts, DATE(ts), HOUR(ts), MINUTE(ts)
FROM
 prec 
WHERE MINUTE(ts) = 0;

結果：

rain    ts  DATE(ts)    HOUR(ts)    MINUTE(ts)
4.2     2021-10-04 12:00:00     2021-10-04  12  0
2.6     2021-10-04 13:00:00     2021-10-04  13  0
1.8     2021-10-04 14:00:00     2021-10-04  14  0

因此，MINUTE(ts) = 55缺少數據！

你可能會更好地採用累積方法來計算，比如說，一年的沉澱，然後做減法。使用視窗函式 (LAG()/LEAD())，這應該很容易！

引用自：https://dba.stackexchange.com/questions/300549