sql - 在 SQL 中选择时间戳范围

Question

我试图在 SQL postgres 的特定日期范围内选择特定时间范围。请参阅下面的代码，它在“10:00:00”上给出了错误。每列的数据类型是:“余额”的数字，“货币”的字符变化(255)，“created_at”没有时区的时间戳(例如:2018-03-20 00:00:00)。

我试过这个链接没有成功。 MySQL select based on daily timestamp range

SELECT SUM(bl.balance) AS balance, bl.currency, bl.created_at
  FROM balance_logs bl
  WHERE bl.balance_scope = 'system' AND
        created_at >= CURRENT_DATE - 2 AND
        created_at < CURRENT_DATE AND
        created_at BETWEEN '10:00:00' AND '11:00:00'
  GROUP BY bl.currency, bl.created_at
  ORDER BY created_at DESC

Answer 1

比较需要作为时间:

SELECT SUM(bl.balance) AS balance, bl.currency, bl.created_at
FROM balance_logs bl
WHERE bl.balance_scope = 'system' AND
      created_at >= CURRENT_DATE - 2 AND
      created_at < CURRENT_DATE AND
      created_at::time BETWEEN '10:00:00'::time AND '11:00:00'::time
GROUP BY bl.currency, bl.created_at
ORDER BY created_at DESC;

但是，我认为最好将WHERE条件写成:

      extract(hour from created_at) = 10