CODEWITHSUNDEEP
sqlpostgresqltimestamp
delalma
delalma

Reputation: 928

Select timestamp range in SQL

I am trying to select a specific time range on a specific days range in SQL postgres. PLease see the code below which gives an error on the '10:00:00'. The type of data for each columns is : numeric for "balance", character varying(255) for "currency", timestamp without time zone for "created_at" (ex: 2018-03-20 00:00:00).

I tried this link without success. 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

Upvotes: 1

Views: 262

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The comparison needs to be as a time:

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;

However, I think it is better to write the WHERE condition as:

      extract(hour from created_at) = 10

Upvotes: 1

Related Questions