如何在SQL查询中只比较日期时间的年份和月份(而忽略日期比较)

使用date_trunc().将两个日期/时间戳截断为一个月后，只要它们的年份和月份匹配，它们就变成等效的:

select distinct t.id from tval t 
where (select min(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3771)
    !=(select min(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3782)
  and id in (select id from id where sid = 2403); 

这种比较不仅有效地忽略了日期，而且忽略了低于一个月精度的任何东西，也忽略了天、小时、分钟、秒和分数.

如果min()是某种妥协，而你实际上想避免ids在同一个月有任何一对3771和3782(而不仅仅是在给定的id的最早出现时)，你可以使用array overlap &&:

select distinct t.id from tval t 
where not 
      (select array_agg(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3771)
    &&(select array_agg(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3782)
  and id in (select id from id where sid = 2403); 

这可能会比not exists更快:

select distinct t.id from tval t 
where not exists
      (select from tval t1 where t1.id = t.id and t1.fid = 3771
       where date_trunc('month',t1.val) in 
          ( select date_trunc('month',t1.val) from tval t1 
            where t1.id = t.id and t1.fid = 3782) )
  and id in (select id from id where sid = 2403);