Marico's space

SQL 日期和时间函数：真实查询实战指南

算法解析 2026-05-04 20:59:48 6

说实话，日期和时间处理是 SQL 里最容易"踩坑"的领域之一。我见过太多经验丰富的开发者，被一个"最近30天"的查询折磨得一头雾水——明明逻辑看起来没问题，结果却莫名其妙。这个话题看起来基础，但数据库引擎之间的差异往往让人防不胜防。

这篇文章覆盖了 PostgreSQL 和 MySQL 两款最常用数据库的日期时间函数，全是实打实的实战案例。文章里的例子基于一个典型的电商数据库，包括订单表和用户表。我会尽量用口语化的方式把这些知识点串起来，让你看完就能用到自己的项目中。

为什么日期时间函数这么重要

想想看，你的查询有多少涉及到时间维度："显示最近7天内的订单"、"计算用户已经注册多久了"、"按月统计营收"。几乎每个有意义的业务问题都有时间这个维度。

真正的挑战在于，不同的 SQL 数据库对日期时间的处理方式各不相同。PostgreSQL 有 DATE_TRUNC 和 AGE，MySQL 有 DATE_FORMAT 和 TIMESTAMPDIFF，标准 SQL 则提供了 EXTRACT 和 CURRENT_TIMESTAMP。搞清楚这些函数的用法和区别，能帮你省下好几个小时的调试时间。

接下来我们用一个贯穿全文的示例：一个电商数据库，包含 orders（订单表）和 customers（用户表）。

-- 示例表结构
CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  name          VARCHAR(100),
  joined_at     TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  order_id      INT PRIMARY KEY,
  customer_id   INT REFERENCES customers(customer_id),
  placed_at     TIMESTAMP NOT NULL,
  shipped_at    TIMESTAMP,
  total_amount  DECIMAL(10,2)
);

1. 获取当前日期和时间

最基础的需求：现在几点了？各大数据库都支持这个功能，但语法略有不同：

-- PostgreSQL
SELECT NOW();                  -- 带时区的当前时间戳
SELECT CURRENT_TIMESTAMP;      -- 与 NOW() 相同，SQL 标准
SELECT CURRENT_DATE;           -- 仅日期（不含时间）
SELECT CURRENT_TIME;           -- 仅时间

-- MySQL
SELECT NOW();                  -- 当前日期时间
SELECT CURDATE();              -- 仅日期
SELECT CURTIME();              -- 仅时间
SELECT UTC_TIMESTAMP();        -- UTC 时间（很实用！）

实战例子：查询今天所有订单。

-- PostgreSQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at::date = CURRENT_DATE;

-- MySQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATE(placed_at) = CURDATE();

注意：对时间戳做日期转换（PostgreSQL 的 ::date 或 MySQL 的 DATE()）是必须的步骤。如果直接写 placed_at = CURRENT_DATE，比较会失败——因为时间戳 2026-04-28 14:33:00 和日期 2026-04-28 根本不相等。

2. 提取日期的组成部分

有时候你只需要时间戳里的某一部分，比如年份、月份或小时。EXTRACT 是 SQL 标准做法，PostgreSQL 和 MySQL 都支持：

-- 标准 SQL — PostgreSQL 和 MySQL 通用
SELECT
  order_id,
  placed_at,
  EXTRACT(YEAR  FROM placed_at) AS order_year,
  EXTRACT(MONTH FROM placed_at) AS order_month,
  EXTRACT(DOW   FROM placed_at) AS day_of_week,  -- PostgreSQL 中 0=周日
  EXTRACT(HOUR  FROM placed_at) AS order_hour
FROM orders;

实战例子：按月分组统计订单，看月度营收趋势。

SELECT
  EXTRACT(YEAR  FROM placed_at) AS yr,
  EXTRACT(MONTH FROM placed_at) AS mo,
  COUNT(*)                      AS order_count,
  SUM(total_amount)             AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

MySQL 专用快捷函数：MySQL 还提供了一些更直观的专用提取函数：

-- MySQL 专用快捷函数
SELECT
  YEAR(placed_at)    AS order_year,
  MONTH(placed_at)   AS order_month,
  DAY(placed_at)     AS order_day,
  HOUR(placed_at)    AS order_hour,
  DAYNAME(placed_at) AS weekday_name   -- 如 "Tuesday"
FROM orders;

3. 日期运算（INTERVAL）

对日期做加减运算是很常见的需求，比如"最近30天的订单"、"未来7天内到期的订阅"等。

-- PostgreSQL：使用 INTERVAL 关键字
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days';

-- MySQL：使用 DATE_SUB() 或直接用 INTERVAL
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL 30 DAY;

-- MySQL 也可以这样写：
SELECT * FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

INTERVAL 支持各种时间单位：

-- PostgreSQL 示例
NOW() + INTERVAL '1 year'
NOW() - INTERVAL '3 months'
NOW() + INTERVAL '2 hours 30 minutes'
'2026-01-01'::date + INTERVAL '90 days'

-- MySQL 示例
DATE_ADD(NOW(), INTERVAL 1 YEAR)
DATE_ADD(NOW(), INTERVAL -3 MONTH)
DATE_ADD(shipped_at, INTERVAL 14 DAY)   -- 预计送达日期

实战例子：筛选出14天前已发货但没有后续追踪的订单。

-- PostgreSQL
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL
  AND shipped_at < NOW() - INTERVAL '14 days';

4. 计算两个日期之间的差值

两个日期之间相差多少天？某个用户注册多久了？这些问题需要日期差值函数。

DATEDIFF（MySQL）

-- MySQL：DATEDIFF 返回天数差
SELECT
  order_id,
  placed_at,
  shipped_at,
  DATEDIFF(shipped_at, placed_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;

TIMESTAMPDIFF（MySQL）

TIMESTAMPDIFF 更灵活——你可以指定返回的单位：

-- MySQL：指定需要的单位
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  TIMESTAMPDIFF(YEAR,  c.joined_at, NOW()) AS years_as_customer,
  TIMESTAMPDIFF(MONTH, c.joined_at, NOW()) AS months_as_customer,
  TIMESTAMPDIFF(DAY,   c.joined_at, NOW()) AS days_as_customer
FROM customers c;

AGE（PostgreSQL）

PostgreSQL 的 AGE() 函数设计得很优雅，直接返回人类可读的间隔：

-- PostgreSQL
SELECT
  customer_id,
  name,
  joined_at,
  AGE(NOW(), joined_at)          AS customer_age,  -- 如 "2 years 3 mons 12 days"
  EXTRACT(DAY FROM AGE(NOW(), joined_at))           AS days_as_customer
FROM customers;

实战例子：找出注册超过1年但最近90天没有下单的用户（适合做挽回营销活动）。

-- PostgreSQL
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  MAX(o.placed_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.joined_at < NOW() - INTERVAL '1 year'
GROUP BY c.customer_id, c.name, c.joined_at
HAVING MAX(o.placed_at) < NOW() - INTERVAL '90 days'
    OR MAX(o.placed_at) IS NULL;

5. 使用 DATE_TRUNC 截断日期（PostgreSQL）

DATE_TRUNC 是 PostgreSQL 的一个强大功能，特别适合按时间段分组数据。它会把时间戳"向下取整"到指定时间段的起始点：

SELECT DATE_TRUNC('month', NOW());
-- 结果：2026-04-01 00:00:00+00

SELECT DATE_TRUNC('week',  NOW());
-- 结果：2026-04-27 00:00:00+00（本周起始，周一）

SELECT DATE_TRUNC('year',  NOW());
-- 结果：2026-01-01 00:00:00+00

支持的精度包括：microseconds、milliseconds、second、minute、hour、day、week、month、quarter、year、decade、century。

实战例子：生成周营收报告，时间段划分干净整齐：

-- PostgreSQL
SELECT
  DATE_TRUNC('week', placed_at)  AS week_start,
  COUNT(*)                        AS orders,
  SUM(total_amount)               AS revenue,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE placed_at >= NOW() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week'

 Powered By Marico's space |  晋ICP备2022011647号 |  技术合作: look@marico.cc TEL: +86 18660050334