前言
使用 SQL 进行业务数据计算时，经常会遇到两个概念：时间范围 和 时间粒度 。以 最近一天的每小时的用户访问人数 为例：
- 最近一天 是时间范围
- 每小时 是时间粒度
常见的时间范围：最近五分钟、最近一小时、最近一天、最近一周、最近一月、最近一年、截止到今天、截止到本周、截止到本月、截止到今年。
常见的时间粒度：五分钟、小时、天、周、月、年。
大多数情况下，我们需要根据计算时间和时间范围，计算出业务数据的开始时间和结束时间，用于过滤业务数据；然后再根据业务数据的业务时间和时间粒度，计算出业务时间点，用于分组统计业务数据。
假设用户访问表（user_visit）记录如下：
iduidtimestamp1u12022-09-19 15:10:582u22022-09-19 16:24:193u12022-09-20 01:04:034u22022-09-20 02:12:365u12022-09-20 02:35:036u12022-09-20 03:10:27
使用 最近一天 过滤数据，开始时间：2022-09-20 00:00:00，结束时间：2022-09-21 00:00:00，SQL 伪代码：
SELECT*FROM
user_visit
WHEREtimestamp>="2022-09-20 00:00:00"ANDtimestamp<"2022-09-21 00:00:00"
过滤结果：
iduidtimestamp3u12022-09-20 01:04:034u22022-09-20 02:12:365u12022-09-20 02:35:036u12022-09-20 03:10:27
过滤后的业务数据，使用 小时 将业务时间转换成业务时间点，转换结果：
iduidtimestamp3u12022-09-20 01:00:004u22022-09-20 02:00:005u12022-09-20 02:00:006u12022-09-20 03:00:00
按小时分组统计用户访问人数，SQL 伪代码：
SELECTtimestamp,COUNT(DISTINCT(uid))AS uids
FROM
user_visit
GROUPBYtimestamp
统计结果：
timestampuids2022-09-20 01:00:0012022-09-20 02:00:0022022-09-20 03:00:001
整个过程涉及两个关键的时间计算：
- 根据计算时间和时间范围，计算业务数据开始时间和结束时间
- 根据业务时间和时间粒度，计算业务时间点
这两个时间的计算均需要通过 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-1HOUR),'%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-1DAY),'%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-1MONTH),'%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-1YEAR),'%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(),INTERVAL1MONTH),'%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(),INTERVAL1YEAR),'%Y-01-01 00:00:00')
时间粒度
五分钟
参考时间范围为最近五分钟的结束时间的计算方法。
小时
参考时间范围为最近一小时的结束时间的计算方法。
天
参考时间范围为最近一天的结束时间的计算方法。
周
参考时间范围为最近一周的结束时间的计算方法。
月
参考时间范围为最近一月的结束时间的计算方法。
年
参考时间范围为最近一年的结束时间的计算方法。
结语
时间范围和时间粒度的计算虽然不是什么技术难点，却是数据分析 SQL 语句中极其重要的组成部分。不同数据库之间的日期时间函数的支持程度差异较大，具体使用时很容易混淆，如果平时可以多记录多总结，则可以幅度提升开发效率。
版权归原作者 萌猫他爸 所有， 如有侵权，请联系我们删除。