postgres unable to get query filter by time and date viz

Question

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

Jim Jones · Accepted Answer · 2020-01-25 12:05:46Z

1

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

edited Jan 25, 2020 at 12:05

answered Jan 24, 2020 at 9:48

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nebojsa Susic · Accepted Answer · 2020-01-24 09:48:11Z

0

You can run some query like this

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#';

answered Jan 24, 2020 at 9:48

Nebojsa Susic

1,2601 gold badge9 silver badges13 bronze badges

Comments

GMB · Accepted Answer · 2020-01-25 12:11:36Z

0

You can use Postgres' date function extract():

select id,created_at 
from tickets 
where 
    created_at between '2020-01-23' and '2020-01-25'
    and extract(hour from created_at) between 5 and 6

edited Jan 25, 2020 at 12:11

user330315

answered Jan 24, 2020 at 9:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

postgres unable to get query filter by time and date viz

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related