选择没有一天只有几个小时的行

Question

我有一个包含日期列的表。我想检索不属于周日第7,8,9小时，周一第8,9,10小时，等等的记录。我该怎么做呢?

如果我使用"and"子句和"not in"，那么所有的行就会消失，而不仅仅是几个小时。

select * from xyz
where (EXTRACT(dow from created) not in (0)
and    EXTRACT(HOUR FROM created) not in (8,9,10)) 

那么星期天的所有行都消失了。

Answer 1

假设timestamp列:

SELECT *
FROM   xyz
WHERE  CASE EXTRACT(dow FROM created)
         WHEN 0 THEN EXTRACT(h FROM created) NOT IN (7,8,9)
         WHEN 1 THEN EXTRACT(h FROM created) NOT IN (8,9,10)
         -- other day ...
         ELSE TRUE  -- remaining days pass as a whole
       END

Answer 2

select *
from xyz
where
    (
        EXTRACT(dow from created) = 0
        and
        EXTRACT(HOUR FROM created) not in (7,8,9)
    )
    or
    (
        EXTRACT(dow from created) = 1
        and
        EXTRACT(HOUR FROM created) not in (8,9,10)
    )
    or extract(dow from created) in (2,3,4,5,6)