CREATE TABLE my_table (
    ...
    dt          TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    ...
);

-- сегодня
WHERE dt >= date_trunc('day',NOW())
-- вчера
WHERE dt < date_trunc('day',NOW()) AND dt >= date_trunc('day',NOW()-'1day'::interval)
-- неделя
???
-- прошлая неделя
???
-- месяц
WHERE dt >= date_trunc('mon',NOW())
-- прошлый месяц
WHERE dt < date_trunc('mon',NOW()) AND dt >= date_trunc('mon',NOW()-'1mon'::interval)
-- год
WHERE dt >= date_trunc('year',NOW())
-- прошлый год
WHERE dt < date_trunc('year',NOW()) AND dt >= date_trunc('year',NOW()-'1y'::interval)