如何查询日期拆分为多字段的表中前一天的数据？

问题场景 #

现有students表，日期信息拆分为xYear（年）、xMonth（月）、xDay（日）三个独立字段。假设当前日期为2023年1月13日，需要查询前一天（即2023年1月12日）的所有记录。

表结构：

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  xYear INTEGER,
  xMonth INTEGER,
  xDay INTEGER  
);

示例数据：

预期结果：

解决方案 #

方法1：拼接日期字段后与前一天日期比较

将表中的xYear、xMonth、xDay拼接成标准日期格式，再和前一天的日期进行匹配。不同数据库的日期拼接函数略有差异：

MySQL

SELECT *
FROM students
WHERE STR_TO_DATE(CONCAT(xYear, '-', xMonth, '-', xDay), '%Y-%m-%d') = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

如果当前日期固定为2023-01-13，也可以直接写死目标日期：

SELECT *
FROM students
WHERE STR_TO_DATE(CONCAT(xYear, '-', xMonth, '-', xDay), '%Y-%m-%d') = '2023-01-12';

PostgreSQL

SELECT *
FROM students
WHERE TO_DATE(CONCAT(xYear, '-', xMonth, '-', xDay), 'YYYY-MM-DD') = CURRENT_DATE - INTERVAL '1 day';

SQLite

SELECT *
FROM students
WHERE DATE(xYear || '-' || xMonth || '-' || xDay) = DATE('now', '-1 day');

方法2：直接匹配前一天的年、月、日字段

如果不需要动态适配任意日期，针对题目中的固定场景，直接匹配目标日期的三个字段即可：

SELECT *
FROM students
WHERE xYear = 2023
  AND xMonth = 1
  AND xDay = 12;

如果需要动态获取前一天的年、月、日（适配任意当前日期），可以用数据库函数提取前一天的年月日信息：

MySQL

SELECT *
FROM students
WHERE xYear = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
  AND xMonth = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
  AND xDay = DAY(DATE_SUB(CURDATE(), INTERVAL 1 DAY));

两种方法对比 #

• 方法1：无需手动处理跨月、跨年逻辑（比如1月1日的前一天是上年12月31日），通用性强；但拼接日期会触发函数计算，若xYear、xMonth、xDay字段建有联合索引，可能无法被利用。
• 方法2：直接匹配字段，能高效利用联合索引；但动态场景下需要确保日期计算逻辑正确处理跨月跨年情况。

内容的提问来源于stack exchange，提问作者Rajnesh Thakur