sql - 通过created_at搜索时，PostgreSQL的结果不正确

我有以下疑问：

SELECT
  COUNT(sr.id) AS total,
  COUNT(sr.id) FILTER (where (raw #>> '{survey, declined}' IS NULL) OR (raw #>> '{survey, declined}' = 'false')) AS completed,
  COUNT(sr.id) FILTER (WHERE (raw #>> '{survey, declined}' = 'true')) AS denied
FROM survey_results sr
LEFT JOIN clients c ON c.id = sr.client_id
LEFT JOIN facilities f ON f.client_id = c.id
WHERE
    sr.created_at >= '2005-07-01T08:00:00+00:00' AND
    sr.created_at <= '2005-08-02T07:59:59+00:00' AND
    4 IS NULL OR sr.client_id = 4 AND
    NULL IS NULL OR f.id = NULL
;

问题是不使用在筛选器中创建的那些。它还返回在不同时间创建的记录。我该怎么解决？

最佳答案

您需要括号：

WHERE (sr.created_at >= '2005-07-01T08:00:00+00:00' AND
       sr.created_at <= '2005-08-02T07:59:59+00:00'
      ) AND
      (4 IS NULL OR sr.client_id = 4) AND
      (NULL IS NULL OR f.id = NULL)

虽然没有必要，但我将使用带有tz的显式时间戳编写tis，并将逻辑简化为：

WHERE (sr.created_at >= '2005-07-01T08:00:00+00:00'::timestamptz AND
       sr.created_at < '2005-08-02T08:00:00+00:00'::timestamptz
      ) AND
      (4 IS NULL OR sr.client_id = 4) AND
      (NULL IS NULL OR f.id = NULL)

关于sql - 通过created_at搜索时，PostgreSQL的结果不正确，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45954904/