SQL 时间范围和时间粒度

时间:2022-09-21 20:39:01

使用 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 语句中极其重要的组成部分。不同数据库之间的日期时间函数的支持程度差异较大,具体使用时很容易混淆,如果平时可以多记录多总结,则可以幅度提升开发效率。