前言

使用 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 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 語句中極其重要的組成部分。不同資料庫之間的日期時間函數的支援程度差異較大，具體使用時很容易混淆，如果平時可以多記錄多總結，則可以幅度提升開發效率。

SQL 時間範圍和時間粒度

前言

時間型別

日期時間字串

Unix 時間戳

時間範圍

最近五分鐘

最近一小時

最近一天

最近一週

最近一月

最近一年

截止到今天

截止到本週

截止到本月

截止到今年

時間粒度

五分鐘

小時

天

周

月

年

結語