PostgreSQL中如何基于整数类型creationDate筛选近两年两个月内的记录

核心逻辑是：先算出当前UTC时间往前推2年2个月的时间点，再把这个时间转换成和creationDate同格式的整数时间戳，最后用这个整数值作为过滤条件筛选符合要求的记录。

具体实现步骤 #

1. 计算目标UTC时间点
   PostgreSQL支持直接用INTERVAL做时间偏移，要确保用UTC时间计算，避免时区偏差：

   CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '2 years 2 months'
   

   也可以简化成INTERVAL '26 months'（2年2个月合计26个月），效果完全一致。

2. 转换为整数时间戳
   因为creationDate是整数类型，需要把上面的时间转成对应单位的Unix时间戳：

   • 如果creationDate是秒级时间戳（最常见的整数时间戳类型，比如bigint）：

     EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '2 years 2 months'))::bigint
     

     EXTRACT(EPOCH FROM ...)会返回从1970-01-01 UTC到目标时间的秒数，转成bigint和creationDate类型匹配。
   • 如果creationDate是毫秒级时间戳：

     (EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '2 years 2 months')) * 1000)::bigint
     

     乘以1000将秒转成毫秒，再转成bigint即可。

3. 完整查询示例
   假设你的表名为target_table：

   • 秒级时间戳场景：

     SELECT *
     FROM target_table
     WHERE creationDate >= EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '2 years 2 months'))::bigint;
     

   • 毫秒级时间戳场景：

     SELECT *
     FROM target_table
     WHERE creationDate >= (EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '2 years 2 months')) * 1000)::bigint;
     

关键注意事项 #

• 验证时间戳单位：如果不确定creationDate是秒还是毫秒，可以用这条SQL验证：

  -- 秒级验证
  SELECT TO_TIMESTAMP(creationDate) AT TIME ZONE 'UTC' FROM target_table LIMIT 1;
  -- 毫秒级验证
  SELECT TO_TIMESTAMP(creationDate / 1000) AT TIME ZONE 'UTC' FROM target_table LIMIT 1;
  

• 避免索引失效：不要在creationDate上做函数转换（比如TO_TIMESTAMP(creationDate)），否则会导致表扫描，大幅降低查询效率。始终把目标时间转成整数去匹配creationDate，这样能利用creationDate上的索引。

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