postgres unable to get query filter by time and date viz

I have data like this:-

select id,created_at from tickets where created_at between '2020-01-23' and '2020-01-25';
 id  |         created_at         
-----+----------------------------
 297 | 2020-01-24 06:27:29.892841
 298 | 2020-01-24 06:57:51.710571
 299 | 2020-01-24 08:00:58.168503
 300 | 2020-01-24 08:41:27.063816

I want result to be filter by time i.e within that date range I want record created between 5 to 7

expected result:-

 id  |         created_at         
-----+----------------------------
 297 | 2020-01-24 06:27:29.892841
 298 | 2020-01-24 06:57:51.710571

Answer

Try casting the timestamp column to time and keep your logic.

CREATE TEMPORARY TABLE tickets ( id int, created_at timestamp);
INSERT INTO tickets VALUES
 (297 ,' 2020-01-24 06:27:29.892841'),
 (298 ,' 2020-01-24 06:57:51.710571'),
 (299 ,' 2020-01-24 08:00:58.168503'),
 (300 ,' 2020-01-24 08:41:27.063816');


SELECT id, created_at FROM tickets
WHERE created_at BETWEEN '2020-01-23' and '2020-01-25'
AND created_at::TIME BETWEEN '05:00:00' AND '07:00:00';

 id  |         created_at         
-----+----------------------------
 297 | 2020-01-24 06:27:29.892841
 298 | 2020-01-24 06:57:51.710571
(2 Zeilen)

Using extract(hour ...) is also an alternative, but you lose the ability to consider minutes in your time interval, e.g. 05:42:00–06:55:00