企业官网建设流程全解析

我们团队上个月做年度报表，有个模块的数据怎么都对不上，后来一查，问题出在时间截取上：报表的“今日订单数”统计的是全天数据，而运营看板那边的“今日订单数”却只统计到了下午两点之前，两边用的截取方式不一样。这种坑，你有没有踩过？

其实在SQL开发里，日期和时间的截取是少不了的活儿。你可能是给报表按天分组，也可能要做运营看板按小时统计，还可能在查询里指定某一段日期范围。不管哪种场景，如果对日期时间函数的底层逻辑理解不够透，写出来的SQL要么性能拉胯，要么结果跟预期对不上。

这篇文章就以“数据库SQL实现截取时间段和日期”为主题，把我在SQL Server、MySQL、Oracle、PostgreSQL这几类数据库里常用的时间截取写法、设计思路和避坑经验完整梳理一遍。适合平时要写报表SQL、做数据统计、处理日志数据的朋友参考，尤其是那些被日期分组折腾过的人，这篇文章大概率对你有用。

1. 我们说的“截取时间”到底在截什么

1.1 日期时间的本质是一个数字

很多新手对日期时间的理解停留在“字符串”层面，总觉得日期就是一个带横杠和冒号的文本。这是很多SQL写错、查错、性能出问题的根源。

实际上，数据库里的日期时间类型，底层存储是一串数字，或者更准确地说，是通过数字记录某个时间点距离基准时间的偏移量。比如在SQL Server里，datetime类型实际存储的是两个4字节整数，一个记录自1900年1月1日以来的天数，另一个记录当天的时钟刻度数；在MySQL里，datetime和timestamp虽然显示格式相同，但存储方式完全不同；PostgreSQL的timestamp则是微秒精度的整数计数。

理解了这一点，“截取日期”的本质就清晰了：不是去“切字符串”，而是对那个数值做某种归整或取整操作。比如去掉时分秒，本质是把当天凌晨零点作为该日期在当天的代表值；取月份，本质是取该月1号零点；按小时归组，本质是把分钟和秒清零。

1.2 截取分三类：截日期、截时间、截时段

日常开发里，最常见的截取需求可以分成三类：

第一类：只保留日期部分。比如一张表里记录的是“2024-06-18 14:32:09”，但你想按“2024-06-18”分组统计，这就需要把时分秒去掉。

第二类：只保留时间部分。你要分析“每天哪个小时的访问量最高”，那么日期部分没意义，需要把时分秒提取出来，忽略日期。

第三类：判断某条记录属于哪个时间段。比如公交刷卡记录，你要判断它是“早高峰”还是“晚高峰”；或者按“5分钟一个窗口”统计QPS。这种需求不光是去掉某些字段，还要算归属区间。

这三类需求，在不同的数据库里写法差异很大，很容易踩坑。下面我按数据库分类来讲。

2. 四大主流数据库的日期截取函数横向对比

2.1 SQL Server：CONVERT和FORMAT的组合拳

在SQL Server环境（尤其是公司还在用SQL Server 2008 R2或者2012的）里，最常用的日期截取方法是利用CONVERT函数将日期时间转为指定格式的字符串。这里有个底层逻辑要注意：CONVERT返回的其实还是字符串，只不过SQL Server允许它在某些语境下隐式转回日期，所以大多数人把它当日期用。

比如，想拿到“今天”并去掉时分秒，可以这么写：

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120);

解释一下：GETDATE()拿当前系统时间，120这个样式值代表“yyyy-mm-dd hh:mi:ss”这种格式，先转成VARCHAR(10)截出前10位也就是年月日，再用CONVERT把它转回DATETIME。这种写法在旧版SQL Server里非常管用，也容易理解。

如果你的SQL Server版本是2012以上，可以用FORMAT函数，但我要劝你谨慎：

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');

FORMAT的问题在于它走的是.NET的CLR格式化，在大数据量场景下性能很差。如果你在一个百万行的查询里用FORMAT做分组依据，速度会明显变慢，实测下来可能比CONVERT慢3到5倍以上。所以我的建议是：能用CONVERT就别用FORMAT。

截取时间的另一种常见需求是只保留时间部分，比如只取“14:32”：

SELECT CONVERT(VARCHAR(5), GETDATE(), 108);

108对应“hh:mi:ss”，VARCHAR(5)取前5位就是小时和分钟。不过这种结果是字符串，做时间排序时要注意，字符串排序和真正的时间排序在跨天场景下可能不一致，但这种场景比较少，一般够用。还有一个小技巧，要拿到当天的零点可以直接用DATEADD和DATEDIFF组合：

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);

这句的含义是：先计算当前日期距离1900-01-01（也就是0）有多少天，再把那么多天加回1900-01-01。这是SQL Server里很多老手钟爱的写法，因为它在去除时间部分的同时，得到的还是DATETIME类型，方便后续运算。

2.2 MySQL：DATE_FORMAT是万金油但要用对位置

MySQL里最常用的就是DATE_FORMAT函数，它把一个日期时间按照你给的格式模板转成字符串。比如：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); SELECT DATE_FORMAT(NOW(), '%H:%i'); SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00');

第三个写法非常实用：它是按小时归整的意思，也就是把分钟和秒清零，保留“2024-06-18 14:00:00”这样的值，适合做按小时统计的基础字段。

但这里有个性能陷阱。如果你在WHERE条件里这么写：

SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-06-18';

一旦create_time上有索引，这个查询有极大概率走全表扫描。因为在DATE_FORMAT(create_time)里，你对索引列做了函数转换，导致索引无法被正常匹配。正确做法是直接写成范围条件：

SELECT * FROM orders WHERE create_time >= '2024-06-18 00:00:00' AND create_time < '2024-06-19 00:00:00';

这个坑下文我会再用一整节展开说，因为太重要了。

MySQL里另外两个好用的函数是DATE()和TIME()，一个直接取日期部分，一个取时间部分：

SELECT DATE(NOW()); -- 2024-06-18 SELECT TIME(NOW()); -- 14:32:09

取年份、月份、星期、季度的话，有YEAR()、MONTH()、DAY()、WEEK()、QUARTER()这些现成函数，直接套用就行。另外，EXTRACT这个函数在MySQL和Oracle都支持，写起来思路更统一：

SELECT EXTRACT(YEAR FROM NOW()); SELECT EXTRACT(HOUR FROM NOW());

2.3 Oracle：TO_CHAR和TRUNC是两大主力

Oracle里，最常用的是TO_CHAR，它和MySQL的DATE_FORMAT类似，但格式模板不同。举几个例子：

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:00:00') FROM DUAL;

Oracle的格式符相当丰富，YYYY是四位年、MM是两位月、DD是两位日、HH24是24小时制小时、MI是分钟、SS是秒，还有D代表星期几、IW代表ISO周。用好了几乎可以满足一切展示需求。

不过，TO_CHAR也是把日期转成字符串，如果你需要保留日期类型继续参与运算，那就要用TRUNC函数：

SELECT TRUNC(SYSDATE) FROM DUAL; -- 当天零点 SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; -- 当月1号零点 SELECT TRUNC(SYSDATE, 'WW') FROM DUAL; -- 当年1月1日起第N周的周一

TRUNC的机制很有意思，它是在日期数值上直接做截断，结果仍然是DATE类型，所以可以继续做加减法。比如你要查上个月的起始日期，就可以写：

SELECT TRUNC(SYSDATE, 'MM') - 1 FROM DUAL;

还有TRUNC的按小时截断，需要写'HH'或'HH24'，比如：

SELECT TRUNC(SYSDATE, 'HH24') FROM DUAL;

这个返回的是当前小时零分零秒的时间点，做小时级报表非常方便。

2.4 PostgreSQL：DATE_TRUNC是最优雅的那个

如果你用PostgreSQL，那么恭喜你，它的DATE_TRUNC函数是我用过的几个数据库里语义最清晰、性能也最稳的时间截取函数。语法如下：

SELECT DATE_TRUNC('day', NOW()); SELECT DATE_TRUNC('hour', NOW()); SELECT DATE_TRUNC('minute', NOW()); SELECT DATE_TRUNC('week', NOW()); SELECT DATE_TRUNC('month', NOW());

DATE_TRUNC的第一个参数指定归整粒度，第二个参数指定要处理的时间，返回结果仍然是timestamp类型。它和Oracle的TRUNC思路相似，但表达更直接。比如按小时归整的对比，MySQL要用DATE_FORMAT转字符串，PostgreSQL一行DATE_TRUNC('hour', NOW())搞定，后续还能直接和interval做加减法。

各数据库的时间截取函数，我压成一张表方便你收藏：

这张表不是让你背的，而是提醒你：不同数据库的“截取”思路可以分为两派。一派是字符串格式化派，比如MySQL的DATE_FORMAT、SQL Server的CONVERT、Oracle的TO_CHAR；另一派是数值截断派，比如PostgreSQL的DATE_TRUNC、Oracle的TRUNC、SQL Server的DATEADD/DATEDIFF组合法。理解了这个本质，换数据库时你就不用重新学，只需要找对方法。

3. 时段统计的实战用法：从固定窗口到动态归属

3.1 按天统计的最优写法

按天统计是所有报表需求里最常见的一种。假设有一张订单表orders，字段包括order_id、amount和created_at，现在要统计6月每一天的订单总额。在MySQL里可以这样：

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS day, SUM(amount) AS total_amount FROM orders WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-07-01 00:00:00' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') ORDER BY day;

这里我特别强调：不要在WHERE层面对created_at用函数。我见过很多同事在WHERE里写DATE(created_at) = '2024-06-18'，看起来没问题，一旦数据量过百万，查询时间就是指数级上升。正确思路是先用范围条件把数据圈定在一个区间内，让索引有机会生效，然后才在SELECT和GROUP BY层面对时间做格式化处理。

如果换成PostgreSQL，写法是：

SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount) AS total_amount FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01' GROUP BY DATE_TRUNC('day', created_at) ORDER BY day;

注意PostgreSQL里日期和字符串之间有隐式转换，'2024-06-01'会被转成timestamp，但如果能显式写TIMESTAMP '2024-06-01 00:00:00'，语义会更明确，尤其是涉及跨时区的时候。

3.2 按小时统计时的“时间段”截取思路

按小时统计有一个典型的应用是“业务高峰分析”：想知道一天24个小时里，哪个小时的订单量最大。核心问题就是怎么把2024-06-18 08:23:45归到08:00这一档。

SQL Server写法：

SELECT DATEPART(HOUR, created_at) AS hour_no, COUNT(*) AS order_count FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01' GROUP BY DATEPART(HOUR, created_at) ORDER BY hour_no;

DATEPART(HOUR, created_at)的好处是直接返回0到23的整数，后续你要拿它跟业务定义的“早高峰”（比如7点到9点）做关联，非常方便。

MySQL写法：

SELECT HOUR(created_at) AS hour_no, COUNT(*) AS order_count FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01' GROUP BY HOUR(created_at) ORDER BY hour_no;

如果你不止要小时编号，还要一个“可读的时间段标签”，比如“08:00-09:00”，那可以在SELECT里拼一个字段：

SELECT HOUR(created_at) AS hour_no, CONCAT( LPAD(HOUR(created_at), 2, '0'), ':00-', LPAD(HOUR(created_at) + 1, 2, '0'), ':00' ) AS time_slot_label, COUNT(*) AS order_count FROM orders GROUP BY HOUR(created_at) ORDER BY hour_no;

这里LPAD是左填充的意思，把“8”补成“08”，保证时间标签格式统一。实际项目中，这种时间段标签经常要传给图表前端展示，格式统一能省掉不少前端处理的麻烦。

3.3 按5分钟粒度、自然周、季度的截取

比小时更细的粒度是分钟级，比如监控系统里统计每5分钟的请求量。核心思路：把时间戳归整到它所属的5分钟窗口。

MySQL写法：

SELECT DATE_FORMAT( FROM_UNIXTIME( FLOOR(UNIX_TIMESTAMP(created_at) / 300) * 300 ), '%Y-%m-%d %H:%i:%s' ) AS five_min_window, COUNT(*) AS request_count FROM access_log GROUP BY FLOOR(UNIX_TIMESTAMP(created_at) / 300) ORDER BY five_min_window;

看着复杂，拆开解释就简单了：UNIX_TIMESTAMP把日期时间变成秒数，FLOOR(... / 300) * 300把它向下归整到最近的5分钟边界（300秒=5分钟），再用FROM_UNIXTIME转回日期时间，最后格式化。这里的数学逻辑是通用的，你要3分钟就把300换成180，要10分钟就换成600。

PostgreSQL里写这个就优雅得多：

SELECT DATE_TRUNC('minute', created_at) - (EXTRACT(MINUTE FROM created_at)::int % 5) * INTERVAL '1 minute' AS five_min_window, COUNT(*) AS request_count FROM access_log GROUP BY five_min_window ORDER BY five_min_window;

不过我个人觉得PostgreSQL这个写法还是有点绕。其实PostgreSQL 14以上可以直接用date_bin函数：

SELECT date_bin('5 minutes', created_at, TIMESTAMP '2000-01-01') AS five_min_window, COUNT(*) AS request_count FROM access_log GROUP BY five_min_window ORDER BY five_min_window;

date_bin的语义是“把时间戳对齐到以第三个参数为基准的固定间隔上”，用起来特别顺手。

按自然周统计，MySQL可以用YEARWEEK(created_at)：

SELECT YEARWEEK(created_at, 1) AS week_no, SUM(amount) AS total_amount FROM orders GROUP BY YEARWEEK(created_at, 1);

第二个参数1表示周一作为每周的第一天，这个参数挺关键，因为有些业务的口径是周日为一周开始，你不指定的话数据库默认可能和你业务不一致。Oracle可以用TO_CHAR(created_at, 'IYYY-IW')来取ISO周，同样是周一为一周开始。

按季度统计，MySQL里可以用CONCAT(YEAR(created_at), 'Q', QUARTER(created_at))，PostgreSQL更直接，有DATE_TRUNC('quarter', created_at)，Oracle用TO_CHAR(SYSDATE, 'YYYY"Q"Q')，SQL Server则是CONCAT(YEAR(GETDATE()), 'Q', DATEPART(QUARTER, GETDATE()))。思路都差不多：要么截断到季度起点，要么拼一个季度标签。

3.4 动态时间段：近7天、上个月、去年同期

做报表经常要查“近7天”“上个月”“去年同期”，这种动态时间范围的SQL写起来有个套路：用当前时间作为锚点，前推N个时间单位。

以MySQL为例：

-- 近7天（包括今天） SELECT * FROM orders WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY); -- 上个月 SELECT * FROM orders WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01'); -- 去年同期 SELECT * FROM orders WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-01-01') AND created_at < DATE_FORMAT(CURDATE(), '%Y-01-01');

这里有个细节：近7天如果用DATE_SUB(CURDATE(), INTERVAL 7 DAY)，那实际上取的是从7天前的零点到当前时刻，如果你希望“取到昨天24点”，写法就不一样了。这种边界问题非常容易导致报表数据差一天，我自己的习惯是先在开发环境用已知数据验一遍边界，再交给业务方，宁可多花十分钟验证，也不要上线后被业务方找上门。

在SQL Server里，动态日期范围常用DATEADD和DATEDIFF配合：

-- 上个月第一天 SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0); -- 上个月最后一天（即本月第一天减1天） SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

这两个写法在纯SQL Server环境里效率极高，因为全程没有字符串转换，都是纯数值运算，推荐索引友好度也比较高。

4. WHERE条件里截取时间的性能陷阱

4.1 对列使用函数等于告诉数据库“别用索引”

这一点前面提过，但值得用一整节来讲，因为它太容易犯错了。先看一个典型案例。有个订单表有100万行数据，create_time字段上有索引，你要查某一天的数据。两种写法：

写法一：

SELECT * FROM orders WHERE DATE(create_time) = '2024-06-18';

写法二：

SELECT * FROM orders WHERE create_time >= '2024-06-18 00:00:00' AND create_time < '2024-06-19 00:00:00';

写法一在MySQL里，因为DATE(create_time)是对列做了函数运算，优化器很难直接把索引定位到具体范围，大概率要走全表扫描。写法二是一个标准半开区间查询，优化器能直接利用B+树索引定位到起始位置然后顺序扫描，性能差距可能达到几十倍。

这不是某一种数据库的问题，而是关系型数据库的通用规则。SQL Server、Oracle、PostgreSQL都有类似的优化行为。哪怕你在PostgreSQL里对created_at列做::date类型转换再比较，同样会限制索引的使用。

4.2 时间段范围查询的标准姿势

正确的做法是用一个半开区间来表示一个时间段。什么叫半开区间？就是“包含起始点，不包含结束点”：

WHERE create_time >= '2024-06-18 00:00:00' AND create_time < '2024-06-19 00:00:00'

这样写有三个好处：

第一，不会漏数据。如果你用BETWEEN '2024-06-18 00:00:00' AND '2024-06-18 23:59:59'，会漏掉23:59:59.500这类毫秒级记录。别看单条数据无所谓，一旦碰上秒杀、支付这种毫秒级并发场景，漏的可能是几万条。所以我的习惯是：日期区间一律用>=和<组合，不用BETWEEN处理带时间部分的查询。

第二，索引友好。数据库可以直接走索引范围扫描。

第三，代码语义清晰。任何人看到“大于等于起点、小于终点”，都能立刻明白这个时间段的边界逻辑。

同样的原则适用于按月查询：不要写WHERE MONTH(create_time) = 6，要写WHERE create_time >= '2024-06-01' AND create_time < '2024-07-01'。

4.3 一个隐式类型转换引发的全表扫描案例

还有一种更隐蔽的情况：隐式类型转换。比如你的create_time列是datetime类型，但你写了：

WHERE create_time = '2024-06-18'

这个写法，数据库会尝试把字符串'2024-06-18'转换成datetime，那就是2024-06-18 00:00:00。这条记录确实存在才会被查出来，但“6月18号一整天”的数据全查不到。更揪心的是，如果字段类型是varchar，你拿它和日期值比较，数据库可能逐个把列值转成日期再比，那索引也会失效。

我处理过一个线上慢SQL，一个本来几十毫秒的查询，某次上线后变成了十几秒，排查下来就是程序员改了字段类型，从datetime改成datetime2，但查询里还带着隐式转换的地方。所以我的经验是：在时间字段上，显式告诉数据库你要比较的类型，不要依赖隐式转换。比如明确写AND create_time >= CAST('2024-06-18 00:00:00' AS DATETIME2)，省得数据库去猜。

5. 典型业务场景实战：订单统计、日志分析、考勤报表

5.1 订单表按日期和时段统计销售额

运营经常要看的“每日分时销售汇总”，实际上就是把日期和时间段两个维度结合起来。以MySQL为例，我想看6月18日这一天的每个小时的销售额：

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS day, HOUR(created_at) AS hour_no, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE created_at >= '2024-06-18 00:00:00' AND created_at < '2024-06-19 00:00:00' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d'), HOUR(created_at) ORDER BY hour_no;

这个SQL虽然简单，但有几个点很关键：WHERE条件用了半开区间，所以数据一条不漏；GROUP BY里两个维度可以同时生效；ORDER BY hour_no能保证图表X轴顺序不乱。

如果数据库是PostgreSQL，我会写成：

SELECT DATE_TRUNC('day', created_at) AS day, EXTRACT(HOUR FROM created_at)::int AS hour_no, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE created_at >= TIMESTAMP '2024-06-18 00:00:00' AND created_at < TIMESTAMP '2024-06-19 00:00:00' GROUP BY DATE_TRUNC('day', created_at), EXTRACT(HOUR FROM created_at) ORDER BY hour_no;

如果还要按“节假日/工作日”对比，那就需要关联一张日期维表。这属于数据仓库的常规操作，但即使只是业务库，我也建议建一张简单的dim_date表，包含日期、星期几、是否节假日、属于哪个季度、属于哪个财年等字段。有了这张表，很多复杂的统计需求都变成了简单的JOIN，SQL会清爽很多，性能也好，因为日期维表很小，可以常驻内存。

5.2 日志表按5分钟粒度统计QPS

日志表是另一个典型的按时间截取场景。假设有一张access_log表，记录了每次HTTP请求的request_time、path和status_code，你想分析某天整体的访问量，按5分钟粒度看趋势。MySQL写法：

SELECT DATE_FORMAT( FROM_UNIXTIME( FLOOR(UNIX_TIMESTAMP(request_time) / 300) * 300 ), '%H:%i' ) AS time_point, COUNT(*) AS request_count, ROUND(COUNT(*) / 300, 2) AS qps FROM access_log WHERE request_time >= '2024-06-18 00:00:00' AND request_time < '2024-06-19 00:00:00' GROUP BY FLOOR(UNIX_TIMESTAMP(request_time) / 300) ORDER BY time_point;

这里COUNT(*) / 300算的是平均每秒请求数，也就是QPS。FLOOR(UNIX_TIMESTAMP(request_time) / 300)返回的是一个整数，代表从1970年到当前时间一共有多少个5分钟窗口，这个整数作为分组键非常高效，因为整数的比较和哈希都比日期时间类型更快。

我之前在优化一个监控看板时，就是把分组键从DATE_FORMAT(request_time, '%Y-%m-%d %H:%i')换成了FLOOR(UNIX_TIMESTAMP(request_time) / 300)，查询时间从3秒降到了200毫秒左右。原因很简单：字符串分组比整数分组慢得多，因为它们需要计算哈希值、比较字符串内容，而整数分组基本就是一次哈希计算。

5.3 考勤表里的迟到早退判断

考勤系统是时间段判断的经典场景。比如你有一张打卡记录表attendance，字段有employee_id、punch_time。公司规定上午9点上班，超过9点算迟到。你要统计某个月每个员工的迟到次数，SQL可以这么写：

SELECT employee_id, COUNT(*) AS late_count FROM attendance WHERE punch_time >= '2024-06-01 00:00:00' AND punch_time < '2024-07-01 00:00:00' AND DATE_FORMAT(punch_time, '%H:%i:%s') > '09:00:00' AND DAYOFWEEK(punch_time) BETWEEN 2 AND 6 GROUP BY employee_id;

这里用了两个时间截取相关的技巧：DATE_FORMAT提取时间部分做字符串比较，判断是否晚于9点；DAYOFWEEK返回1到7（1是周日、7是周六），用BETWEEN 2 AND 6排除周末。不过要注意：如果公司定义的“工作日”不完全是周一到周五，比如调休的周六上班，那这里的SQL就不够用，需要用工日表来补充。

另一个思路是用TIME(punch_time) > '09:00:00'替代DATE_FORMAT，语义更清晰，因为TIME()函数就是专门提取时间部分的，可读性更好一点。MySQL支持，SQL Server可以用CAST(punch_time AS TIME)，写法略有差异。

5.4 分时汇总的扩展：每个时段的订单数与客单价

有时候，运营会要“每个小时段的订单数和客单价”，这里的“时段”是0点到23点的某一小时，不要求具体是哪一天，那么我们就要把日期部分去掉，只看小时维度的统计：

SELECT HOUR(created_at) AS hour_no, COUNT(*) AS order_count, ROUND(SUM(amount) / COUNT(*), 2) AS avg_order_value FROM orders WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND created_at < CURDATE() GROUP BY HOUR(created_at) ORDER BY hour_no;

这种SQL在业务上很有价值，运营一看就知道“晚上9点到10点是下单高峰，需要加配送运力”。但要注意，ROUND(SUM(amount) / COUNT(*), 2)的计算依赖COUNT(*)，如果某个小时段一条订单都没有，那这个小时在结果里是不会出现的。为了图表美观，你可能需要在一张24小时数字表上做LEFT JOIN，把缺失的小时补0。这也是我强调维表重要的原因之一：有了维表，各种缺失数据的补全是手到擒来的事。

6. 容易踩的坑：跨年跨月、时区、边界值和NULL

6.1 跨年跨月时，用“周”做统计可能翻车

很多业务是按“自然周”统计的，但如果你直接用WEEK(created_at)或者YEARWEEK(created_at)，跨年的时候很容易出问题。比如2023年12月31日恰好是周日，按照ISO标准它是2023年的第52周；但某些业务口径可能把它归到2024年第1周。不同数据库、不同参数，对“第一周”的定义都可能不一样。

MySQL的WEEK()函数有mode参数，从0到7，每种模式对“一周从哪天开始”以及“第一周最少几天”的定义都不同。默认的WEEK()在部分模式下，跨年那一周的处理可能和预期不一致。所以我的建议是：

1. 业务上先统一“周”的定义，是一周从周一开始还是周日开始；
2. SQL里明确写WEEK(created_at, 1)或YEARWEEK(created_at, 1)，永远不要用默认参数；
3. 跨年报表里，最好把年份和周数拼接成一个字段，比如YEARWEEK(created_at, 1)，这样2023年第52周和2024年第1周不会混淆。

在Oracle里可以用IW来取ISO周，前面提过。PostgreSQL的DATE_TRUNC('week', created_at)则始终以周一作为一周起点。不同数据库的口径不一致，迁移的时候一定要重新验一遍数据。

6.2 时区问题：存的是UTC，查的是本地时间

很多系统的数据库为了统一标准，存储的日期时间是UTC时间，但中国业务场景通常要+8小时，也就是东八区时间。如果你在查询时直接用WHERE request_time >= '2024-06-18 00:00:00'，查出来的数据实际上是对应UTC的6月18号，换算成北京时间就是6月18号早上8点到6月19号早上8点，直接错了一天。

这种问题排查起来特别隐蔽，因为看起来SQL完全没毛病。我见过不少案例，都是统计口径对不齐才发现的。处理方式有两种：

第一种，在SQL里显式做时区转换。MySQL可以这样：

SELECT DATE_FORMAT(CONVERT_TZ(request_time, '+00:00', '+08:00'), '%Y-%m-%d') AS day FROM access_log WHERE request_time >= CONVERT_TZ('2024-06-17 16:00:00', '+08:00', '+00:00') AND request_time < CONVERT_TZ('2024-06-18 16:00:00', '+08:00', '+00:00');

其实就是把业务上的“北京时间6月18日0点到6月19日0点”转换成UTC的“6月17日16点到6月18日16点”，然后去查库。这样做的好处是索引友好，因为你没有对列本身做函数操作，只是对查询参数做了转换。

第二种，推荐在表设计或数仓分层时，统一约定“库内存储什么时区、展示时用什么时区”，最好固化到文档里。数据开发每天和时区打交道，没有统一约定，迟早出事。

6.3 边界值查询：23:59:59并不够

前面提过BETWEEN '2024-06-18 00:00:00' AND '2024-06-18 23:59:59'会漏掉23:59:59.500甚至23:59:59.999这种记录。如果字段是datetime2(7)，精度可以达到100纳秒，你用23:59:59做上界，几乎肯定会漏数据。

更可靠的方案是直接用半开区间。如果你一定要用BETWEEN，那上界要写成'2024-06-18 23:59:59.999'，但不同数据库能表示的精度不一样，SQL Server的datetime类型精度是3.33毫秒，datetime2(7)精度是100纳秒，你写23:59:59.999在datetime字段上可能被四舍五入成第二天的00:00:00，导致多出第二天的数据。

所以，坚持“下界包含、上界排除”的写法，是最稳妥的。这个原则我在团队里要求所有人必须遵守。

6.4 NULL时间值的处理策略

时间字段为NULL的情况，经常被忽略。比如订单表里pay_time允许为空，代表这个订单还没支付。如果你直接写：

GROUP BY DATE_FORMAT(pay_time, '%Y-%m-%d')

那NULL会被分到一组，这组数据在报表里会变成“一条空的日期”，业务方看到会困惑。我的处理习惯是二选一：

方案一：在写SQL前明确业务口径，不需要统计未支付订单，就在WHERE层加AND pay_time IS NOT NULL，提前过滤掉。

方案二：如果业务方要区分“未支付”和“已支付”，可以给NULL赋一个默认状态标签：

SELECT COALESCE(DATE_FORMAT(pay_time, '%Y-%m-%d'), '未支付') AS pay_day, COUNT(*) AS order_count FROM orders GROUP BY COALESCE(DATE_FORMAT(pay_time, '%Y-%m-%d'), '未支付');

但COALESCE里如果第一个参数是计算字段，可以简化一下，先取格式化后的值再处理。思路反正是一致的：要么提前过滤，要么显式打标。

还有一种情况是时区转换后出现NULL，比如CONVERT_TZ在遇到非法时间值时可能返回NULL，比如夏令时切换导致不存在的凌晨2点。这种时候要提前和业务确认，这些异常时间怎么归类，不要等到出报表了才发现少了一段。

最后再分享一个我的个人习惯：在开发环境下，我会先准备一张已知结果的测试数据表，把各种边界时间点放进去，比如跨年、跨月、闰年2月29日、夏令时切换点、毫秒级时间点，然后跑一遍SQL，对比结果集是否符合预期。这套验证流程花不了多少时间，但能挡掉大量的低级错误。

日期和时间截取这个需求，看似基础，但里面全是细节。你只有真正理解了数据库的存储逻辑，才能写出既正确又高效的SQL。希望这篇文章能在你下次写时间统计报表时，帮你少踩几个坑。