SQL 时间范围和时间粒度

大家好，欢迎来到IT知识分享网。

最近一天是时间范围

每小时是时间粒度

常见的时间范围：最近五分钟、最近一小时、最近一天、最近一周、最近一月、最近一年、截止到今天、截止到本周、截止到本月、截止到今年。

常见的时间粒度：五分钟、小时、天、周、月、年。

大多数情况下，我们需要根据计算时间和时间范围，计算出业务数据的开始时间和结束时间，用于过滤业务数据；然后再根据业务数据的业务时间和时间粒度，计算出业务时间点，用于分组统计业务数据。

假设用户访问表（user_visit）记录如下：

使用 最近一天 过滤数据，开始时间：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" 

过滤结果：

SELECT timestamp, COUNT(DISTINCT(uid)) AS uids FROM user_visit GROUP BY timestamp 

统计结果：

整个过程涉及两个关键的时间计算：

根据计算时间和时间范围，计算业务数据开始时间和结束时间

根据业务时间和时间粒度，计算业务时间点

这两个时间的计算均需要通过 SQL 的 日期时间函数 实现。然而不同的数据库对于日期时间函数的支持程度差异很大，实际的计算过程可能比较繁琐。

本文以阿里云 ODPS 和 RDS 为例，详细说明日期时间函数关于时间范围和时间粒度的计算方法。

时间范围的开始时间是闭区间，结束时间是开区间。

我们建议将业务时间统一转换成 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 时间戳

以时间戳  为例，将其转换成 DATETIME。 

ODPS

FROM_UNIXTIME() 

RDS

FROM_UNIXTIME() 

时间范围

我们使用 当前时间 指代 计算时间，获取当前时间（DATETIME）： 

ODPS

GETDATE() 

RDS

NOW() 

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 秒。 

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') 

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') 时间粒度