时间数据的截断和取整： DATE_TRUNC()

DATE_TRUNC()是个很强大的工具，可以让你把时间值“截断”到某个时间单位。比如说，你可以把时间戳取整到当天、当月、当年、当小时等等。分析数据按周期分组（比如订单按天、月、年统计）的时候特别有用。

你可以把日期和时间想象成一长串文本，里面有小时、分钟、秒。DATE_TRUNC()函数就是把这串“砍掉”多余的部分，只留下你需要的那一段。比如：

• 你想把2023-10-01 15:30:45截断到当天的开始，结果就是2023-10-01 00:00:00。
• 或者你只想保留小时的第一秒，也就是2023-10-01 15:00:00。

语法

DATE_TRUNC()函数的语法长这样：

DATE_TRUNC(field, source)

• field —— 你想把日期“截断”到哪个时间单位。比如year、month、day、hour、minute。
• source —— 你要截断的时间值。可以是TIMESTAMP类型的列，也可以是别的函数的结果，比如NOW()。

简单调用的例子：

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- 结果: 2023-10-01 00:00:00

支持的字段

下面是DATE_TRUNC()支持的一些时间单位：

时间单位越小，截断的结果就越精确。顺便说一句，一周是从星期天开始的哈 :)

DATE_TRUNC()的用法例子

截断到当天的开始。 这个例子里我们把一个时间戳取整到当天的开始：

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- 结果: 2023-10-01 00:00:00

截断到当月的开始。 现在我们把日期截断到当月的开始：

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- 结果: 2023-10-01 00:00:00

截断到当年的开始。 试试把日期取整到当年的开始：

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- 结果: 2023-01-01 00:00:00

和当前时间(NOW())一起用。 如果你想一直用当前的日期和时间，可以把DATE_TRUNC()和NOW()一起用：

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- 结果会根据当前时间变化，比如: 2023-10-01 15:00:00

按月分组订单。 来个更实用的例子。假设我们有个订单表，每条记录都有下单日期。我们想统计每个月的订单数：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

结果：

实际用法场景

按周期分析时间数据：想知道每年、每月、每天有多少用户注册？用DATE_TRUNC()分组就行。

做报表：把时间戳取整能让报表更好看更易读。

比较日期和时间：如果你的时间数据很精确（比如有毫秒），可以先截断到你想要的级别再比较。

用DATE_TRUNC()时常见的坑

用了不支持的字段。比如millisecond这个字段不支持，用了就会报错。

数据类型不对。DATE_TRUNC()只支持时间类型的数据，比如TIMESTAMP。传字符串进去会报错。

取整方式错了。记住DATE_TRUNC()总是把时间截断到指定单位的开始。如果你想四舍五入，得用别的方法。

2

任务

SQL SELF, 第 32 级, 课程 0

已锁定

将时间截断到每月初

将时间截断到每月初

打开