PostgreSQL：如何忽略年份查询datetime字段匹配的记录？

需要检索所有匹配指定日期（忽略年份和秒数）的记录，示例数据如下：

2023-10-15 12:00:00
2023-10-17 12:00:00
2023-10-23 13:23:00 <--- 需返回此记录
2024-10-23 13:23:10 <--- 需返回此记录

当前用下面的代码查询，想找更简洁的写法：

whereRaw('
    EXTRACT(MONTH FROM event.start_at) = ? AND 
    EXTRACT(DAY FROM event.start_at) = ? AND
    EXTRACT(HOUR FROM event.start_at) = ? AND
    EXTRACT(MINUTE FROM event.start_at) = ?',
        [10, 23, 13, 23]
)

1. 日期格式化比对 #

直接把日期字段格式化为MM-DD HH:mm的字符串，和目标值比对，代码更紧凑：

• PostgreSQL/MySQL：

  whereRaw('DATE_FORMAT(event.start_at, "%m-%d %H:%i") = ?', ['10-23 13:23'])
  

  PostgreSQL也可以用TO_CHAR(event.start_at, 'MM-DD HH24:MI')代替DATE_FORMAT。
• SQL Server：

  whereRaw('FORMAT(event.start_at, "MM-dd HH:mm") = ?', ['10-23 13:23'])
  

2. 截断日期到分钟（屏蔽年份） #

用日期截断函数把时间处理到分钟级别，再去掉年份的影响，比如用固定年份做统一比对：

• PostgreSQL：

  whereRaw('DATE_TRUNC(\'minute\', event.start_at)::time + DATE_TRUNC(\'day\', event.start_at)::date - DATE_TRUNC(\'year\', event.start_at)::date = ?', 
           ['2000-10-23 13:23:00'])
  

3. 区间查询（性能优先） #

如果要兼顾查询性能（能命中start_at的索引），可以构造每个年份对应的时间区间，用OR拼接：

// 示例：查询最近10年符合条件的记录
$startYears = range(date('Y') - 9, date('Y'));
$conditions = [];
$bindings = [];
foreach ($startYears as $year) {
    $conditions[] = 'event.start_at BETWEEN ? AND ?';
    $bindings[] = "{$year}-10-23 13:23:00";
    $bindings[] = "{$year}-10-23 13:23:59";
}
whereRaw(implode(' OR ', $conditions), $bindings);

这种写法比格式化函数的查询效率更高，适合数据量较大的场景。

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