SQL 時間範圍和時間粒度
前言
使用 SQL 進行業務數據計算時，經常會遇到兩個概念：時間範圍 和 時間粒度 。以 最近一天的每小時的用戶訪問人數 為例：
- 最近一天 是時間範圍
- 每小時 是時間粒度
常見的時間範圍：最近五分鐘、最近一小時、最近一天、最近一周、最近一月、最近一年、截止到今天、截止到本周、截止到本月、截止到今年。
常見的時間粒度：五分鐘、小時、天、周、月、年。
大多數情況下，我們需要根據計算時間和時間範圍，計算出業務數據的開始時間和結束時間，用於過濾業務數據；然後再根據業務數據的業務時間和時間粒度，計算出業務時間點，用於分組統計業務數據。
假設用戶訪問表（user_visit）記錄如下：
| id | uid | timestamp |
|---|---|---|
| 1 | u1 | 2022-09-19 15:10:58 |
| 2 | u2 | 2022-09-19 16:24:19 |
| 3 | u1 | 2022-09-20 01:04:03 |
| 4 | u2 | 2022-09-20 02:12:36 |
| 5 | u1 | 2022-09-20 02:35:03 |
| 6 | u1 | 2022-09-20 03:10:27 |
使用 最近一天 過濾數據，開始時間：2022-09-20 00:00:00，結束時間：2022-09-21 00:00:00，SQL 偽代碼：
SELECT
*
FROM
user_visit
WHERE
timestamp >= "2022-09-20 00:00:00"
AND timestamp < "2022-09-21 00:00:00"
過濾結果：
| id | uid | timestamp |
|---|---|---|
| 3 | u1 | 2022-09-20 01:04:03 |
| 4 | u2 | 2022-09-20 02:12:36 |
| 5 | u1 | 2022-09-20 02:35:03 |
| 6 | u1 | 2022-09-20 03:10:27 |
過濾後的業務數據，使用 小時 將業務時間轉換成業務時間點，轉換結果：
| id | uid | timestamp |
|---|---|---|
| 3 | u1 | 2022-09-20 01:00:00 |
| 4 | u2 | 2022-09-20 02:00:00 |
| 5 | u1 | 2022-09-20 02:00:00 |
| 6 | u1 | 2022-09-20 03:00:00 |
按小時分組統計用戶訪問人數，SQL 偽代碼：
SELECT
timestamp, COUNT(DISTINCT(uid)) AS uids
FROM
user_visit
GROUP BY
timestamp
統計結果：
| timestamp | uids |
|---|---|
| 2022-09-20 01:00:00 | 1 |
| 2022-09-20 02:00:00 | 2 |
| 2022-09-20 03:00:00 | 1 |
整個過程涉及兩個關鍵的時間計算：
- 根據計算時間和時間範圍，計算業務數據開始時間和結束時間
- 根據業務時間和時間粒度，計算業務時間點
這兩個時間的計算均需要通過 SQL 的 日期時間函數 實現。然而不同的數據庫對於日期時間函數的支持程度差異很大，實際的計算過程可能比較繁瑣。
本文以阿里雲 ODPS 和 RDS 為例，詳細說明日期時間函數關於時間範圍和時間粒度的計算方法。
時間範圍的開始時間是閉區間，結束時間是開區間。
時間類型
阿里雲的 ODPS 和 RDS 都是支持日期時間（DATETIME）類型的，業務數據可以直接使用 DATETIME 存儲業務時間；也可以使用其它數據類型存儲業務時間，常見的有日期時間字符串（STRING）和 Unix 時間戳（INT）。
我們建議將業務時間統一轉換成 DATETIME 類型之後再進行時間計算。
日期時間字符串
以字符串 2022-09-20 15:10:58 例，將其轉換成 DATETIME。
ODPS
TO_DATE('2022-09-20 15:10:58', 'yyyy-mm-dd hh:mi:ss')
RDS
STR_TO_DATE('2022-09-20 15:10:58', '%Y-%m-%d %H:%i:%s')
Unix 時間戳
以時間戳 1663657859 為例，將其轉換成 DATETIME。
ODPS
FROM_UNIXTIME(1663657859)
RDS
FROM_UNIXTIME(1663657859)
時間範圍
我們使用 當前時間 指代 計算時間，獲取當前時間（DATETIME）：
ODPS
GETDATE()
RDS
NOW()
最近五分鐘
以計算時間：2022-09-20 17:07:33 為例，最近五分鐘的業務開始時間應為：2022-09-20 17:00:00，業務結束時間應為：2022-09-20 17:05:00。
ODPS
// 開始時間
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(GETDATE()) / 300 - 1) * 300)
// 結束時間
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(GETDATE()) / 300) * 300)
RDS
// 開始時間
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW()) / 300 - 1) * 300)
// 結束時間
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW()) / 300) * 300)
300 表示 5 分鐘，即：300 秒。
最近一小時
以計算時間 2022-09-20 17:19:57 為例，最近一小時的業務開始時間應為 2022-09-20 16:00:00，業務結束時間應為 2022-09-20 17:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), -1, 'hh'), 'hh')
// 結束時間
DATETRUNC(GETDATE(), 'hh')
RDS
// 開始時間
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 HOUR), '%Y-%m-%d %H:00:00')
// 結束時間
DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
最近一天
以計算時間 2022-09-20 17:31:06 為例，最近一天的業務開始時間應為 2022-09-19 00:00:00，業務結束時間應為 2022-09-20 00:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), -1, 'dd'), 'dd')
// 結束時間
DATETRUNC(GETDATE(), 'dd')
RDS
// 開始時間
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 DAY), '%Y-%m-%d 00:00:00')
// 結束時間
DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
最近一周
以計算時間 2022-09-20 17:48:10 為例，最近一周的業務開始時間應為 2022-09-12 00:00:00，業務結束時間應為 2022-09-19 00:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()) - 7 , 'dd'), 'dd')
// 結束時間
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()), 'dd'), 'dd')
RDS
// 開始時間
DATE_FORMAT(ADDDATE(NOW(), - 7 - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
// 結束時間
DATE_FORMAT(ADDDATE(NOW(), - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
最近一月
以計算時間 2022-09-20 17:57:05 為例，最近一月的業務開始時間應為 2022-08-01 00:00:00，業務結束時間應為 2022-09-01 00:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), -1, 'mm'), 'mm')
// 結束時間
DATETRUNC(GETDATE(), 'mm')
RDS
// 開始時間
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%Y-%m-01 00:00:00')
// 結束時間
DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
最近一年
以計算時間 2022-09-20 18:03:00 為例，最近一年的業務開始時間應為 2021-01-01 00:00:00，業務結束時間應為 2022-01-01 00:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), -1, 'yyyy'), 'yyyy')
// 結束時間
DATETRUNC(GETDATE(), 'yyyy')
RDS
// 開始時間
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 YEAR), '%Y-01-01 00:00:00')
// 結束時間
DATE_FORMAT(NOW(), '%Y-01-01 00:00:00')
截止到今天
以計算時間 2022-09-20 18:12:31 為例，截止到今天的業務開始時間應為 2022-09-20 00:00:00，業務結束時間應為 2022-09-21 00:00:00。
ODPS
// 開始時間
DATETRUNC(GETDATE(), 'dd')
// 結束時間
DATETRUNC(DATEADD(GETDATE(), 1, 'dd'), 'dd')
RDS
// 開始時間
DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
// 結束時間
DATE_FORMAT(ADDDATE(NOW(), 1), '%Y-%m-%d 00:00:00')
截止到本周
以計算時間 2022-09-20 18:16:20 為例，截止到本周的業務開始時間應為 2022-09-19 00:00:00，業務結束時間應為 2022-09-26 00:00:00。
ODPS
// 開始時間
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()), 'dd'), 'dd')
// 結束時間
DATETRUNC(DATEADD(GETDATE(), 7 - WEEKDAY(GETDATE()), 'dd'), 'dd')
RDS
// 開始時間
DATE_FORMAT(ADDDATE(NOW(), - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
// 結束時間
DATE_FORMAT(ADDDATE(NOW(), 7 - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
截止到本月
以計算時間 2022-09-20 18:19:15 為例，截止到本月的業務開始時間為 2022-09-01 00:00:00，業務結束時間應為 2022-10-01 00:00:00。
ODPS
// 開始時間
DATETRUNC(GETDATE(), 'mm')
// 結束時間
DATETRUNC(DATEADD(GETDATE(), 1, 'mm'), 'mm')
RDS
// 開始時間
DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
// 結束時間
DATE_FORMAT(ADDDATE(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
截止到今年
以計算時間 2022-09-20 18:21:09 為例，截止到今年的業務開始時間為 2022-01-01 00:00:00，業務結束時間應為 2023-01-01 00:00:00。
ODPS
// 開始時間
DATETRUNC(GETDATE(), 'yyyy')
// 結束時間
DATETRUNC(DATEADD(GETDATE(), 1, 'yyyy'), 'yyyy')
RDS
// 開始時間
DATE_FORMAT(NOW(), '%Y-01-01 00:00:00')
// 結束時間
DATE_FORMAT(ADDDATE(NOW(), INTERVAL 1 YEAR), '%Y-01-01 00:00:00')
時間粒度
五分鐘
參考時間範圍為最近五分鐘的結束時間的計算方法。
小時
參考時間範圍為最近一小時的結束時間的計算方法。
天
參考時間範圍為最近一天的結束時間的計算方法。
周
參考時間範圍為最近一周的結束時間的計算方法。
月
參考時間範圍為最近一月的結束時間的計算方法。
年
參考時間範圍為最近一年的結束時間的計算方法。
結語
時間範圍和時間粒度的計算雖然不是什麼技術難點，卻是數據分析 SQL 語句中極其重要的組成部分。不同數據庫之間的日期時間函數的支持程度差異較大，具體使用時很容易混淆，如果平時可以多記錄多總結，則可以幅度提升開發效率。
