PostgreSQL how to filtering after where operator

Question

I'm trying to do something like that

SELECT build.id as build_id, build.name, city.city_name as city, hours.day, hours.start, hours.end FROM Buildings AS build 
LEFT JOIN Cities city ON city.id=build.city_id 
JOIN OperatingHours hours ON hours.build_id=build.id 
WHERE city.id = 3
AND ( hours.day=4 AND ('20:00:00') BETWEEN hours.start AND hours.end)
AND ( hours.day=3 AND ('08:00:00') BETWEEN hours.start AND hours.end)

Is it way to do it?

Sample data and desired results would help.

Gordon Linoff
– Gordon Linoff

2019-12-21 17:38:43 +00:00
Commented Dec 21, 2019 at 17:38 — Gordon Linoff
– Gordon Linoff, Commented Dec 21, 2019 at 17:38

Gordon Linoff · Accepted Answer · 2019-12-21 17:37:51Z

1

Assuming that you want buildings that are open at those two times, then use aggregation:

SELECT b.id as build_id, b.name, c.city_name as city
FROM Buildings b LEFT JOIN
     Cities c
     ON c.id = b.city_id JOIN
     OperatingHours h
     ON h.build_id = b.id 
WHERE city.id = 3
GROUP BY b.build_id
HAVING COUNT(*) FILTER (WHERE h.day = 4 AND '20:00:00' BETWEEN h.start AND h.end) > 0 AND
       COUNT(*) FILTER (WHERE h.day = 3 AND '08:00:00' BETWEEN h.start AND h.end) > 0;

If you want to include the hours and days in the results, then you need to aggregate them. I would recommend array_agg().

answered Dec 21, 2019 at 17:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

197291 Over a year ago

Yes, exactly what i need. Thanks!

Collectives™ on Stack Overflow

PostgreSQL how to filtering after where operator

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related