PostgreSQL SELECT Must Match Across Multiple Rows

Question

I have a table that contains a pair of timestamps that represent a timespan. These rows are scoped by user id, and each user can have one or many rows associated with them. This data is generated from an abstract "availability" form that represents when the user is available during the week. I need to input a series of time ranges as a query and return all user ids for whom all rows in the table match. Given this table: ``` CREATE TABLE "public"."availability" ( "id" int4 NOT NULL, "user_id" int4, "starts_at" timestamp(6), "ends_at" timestamp(6), PRIMARY KEY ("id") ) WITH (OIDS=FALSE) ``` and this data: ``` User #1 is available Mon-Tue between 08:00 and 17:00 +----+---------+---------------------+---------------------+ | id | user_id | starts_at | ends_at | +----+---------+---------------------+---------------------+ | 1 | 1 | 2013-03-18 08:00:00 | 2013-03-18 17:00:00 | +----+---------+---------------------+---------------------+ | 2 | 1 | 2013-03-19 08:00:00 | 2013-03-19 17:00:00 | +…

Accepted Answer

SQL Fiddle

This exploits the boolean cast to integer as 0 or 1.

```
select a.*
from
    availability a
    inner join
    (
        select
            user_id,
            sum (
                ('2013-03-18 09:00:00' between starts_at and ends_at
                 and
                 '2013-03-18 16:00:00' between starts_at and ends_at
                )::integer
                +
                ('2013-03-19 08:00:00' between starts_at and ends_at
                 and
                 '2013-03-19 15:45:00' between starts_at and ends_at
                )::integer
            ) period
        from availability
        group by user_id
    ) s on a.user_id = s.user_id
where period >= 2
```

Change the `where` condition to the number of periods to match.

PostgreSQL SELECT Must Match Across Multiple Rows

Solution

Problem