sql - 我可以使用模来指定 postgres 中的间隔吗？

Question

我必须列出自上次更新后两年内未更新的项目。这是作为 cron 作业每天运行一次。

我知道我可以用一些丑陋的东西来做到这一点:

SELECT art_id, art_update FROM items 
WHERE art_update = now()::date - interval '2 years'
OR art_update = now()::date - interval '4 years'
OR art_update = now()::date - interval '6 years'
OR art_update = now()::date - interval '8 years'
OR art_update = now()::date - interval '10 years';

有没有办法通过检查模数区间来避免这种情况？或者其他一些通用的表达方式？

Answer 1

select art_id, art_update
from items 
where art_update in (
    (now() - interval '2 years')::date,
    (now() - interval '4 years')::date,
    (now() - interval '6 years')::date,
    (now() - interval '8 years')::date,
    (now() - interval '10 years')::date
);

或

select art_id, art_update
from items 
where art_update in (
    select d::date
    from generate_series (
        now() - interval '2 years',
        now() - interval '10 years',
        - interval '2 years'
    ) d(d)
);

http://www.postgresql.org/docs/current/static/functions-srf.html