不同的日期給出相同的TIMEDIFF

我正在嘗試計算兩個不同日期之間的時間。我用這個 sql 命令將它計算到秒，FLOOR(TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, post.created)) / 60) 但它為不同的日期回傳相同的值，比如日期2021-11-05 14:49:13和日期2021-12-16 21:31:09都回傳50339新日期的值，這種方法作業得很好，但出于某種原因，它不適用于這些日期。

這是來自資料庫的 JSON 格式的資料。留下從當前時間戳記到專案創建日期的差異

{"Difference":"50339","created":"2021-11-05 14:49:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-11-05 14:49:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:12:47","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:13:16","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:13:44","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:29:58","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:41:33","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:42:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-15 18:58:22","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-15 19:00:46","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 18:26:55","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:08","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"49002","created":"2021-12-18 17:01:37","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"48831","created":"2021-12-18 19:53:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:00","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:01","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:02","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:02","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43466","created":"2021-12-22 13:17:22","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43464","created":"2021-12-22 13:19:57","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43464","created":"2021-12-22 13:20:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"40784","created":"2021-12-24 09:59:46","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38857","created":"2021-12-25 18:06:24","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38827","created":"2021-12-25 18:37:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38826","created":"2021-12-25 18:37:37","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"37542","created":"2021-12-26 16:02:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"37540","created":"2021-12-26 16:03:24","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"35918","created":"2021-12-27 19:05:51","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"35917","created":"2021-12-27 19:06:35","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"18531","created":"2022-01-08 20:52:38","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"18531","created":"2022-01-08 20:52:39","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:11","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:12","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:14","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:16","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:18","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"13193","created":"2022-01-12 13:51:15","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"29","created":"2022-01-21 17:14:50","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"}
]

uj5u.com熱心網友回復：

MySQL 中的TIMEDIFF函式回傳一個TIME值，其范圍為 '-838:59:59' 到 '838:59:59'。

TIME 值的范圍可以從“-838:59:59”到“838:59:59”。

2021-11-05 14:49:13和2021-12-16 21:31:09from的時間差分別2022-01-21 17:44:20是1850:55:07和860:13:11。這兩個都超出了 TIME 值的定義范圍，因此它們將被修剪到最接近的值，即838:59:59.

默認情況下，位于 TIME 范圍之外但在其他方面有效的值將被剪裁到范圍的最近端點。例如，“-850:00:00”和“850:00:00”將轉換為“-838:59:59”和“838:59:59”。

838:59:59=>3020399秒 =>50339分鐘（大約）

要解決此問題，您可以使用TIMESTAMPDIFF函式或UNIX_TIMESTAMP函式（先將值轉換為秒，然后再減去）。

標籤：

上一篇：橫向視圖在bigquery中爆炸

下一篇：SQL-組合SELECT查詢并獲得%輸出