How to Return Only Work Time from Reservations in Postgresql

How to return only work time from reservations in PostgreSql?

You can use generate_series() function in order to mask-out non business hours:

with gaps as (
    select
        upper(during) as start,
        lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap
    from (
        select during
        from reservation
        union all
        select
            unnest(case
                when pyha is not null then array[tsrange(d, d + interval '1 day')]
                when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')]
                when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')]
                else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')]
            end)
        from generate_series(
            '2012-11-14'::timestamp without time zone, 
            '2012-11-14'::timestamp without time zone + interval '2 week', 
            interval '1 day'
        ) as s(d) 
        left join pyha on pyha = d::date
    ) as x 
)
select *
    from gaps
where gap > '0'::interval
order by start

Let me explain some tricky parts:

you dont have to insert dates for sat/sun into pyha table because you can use date_part('dow', d) function. Use pyha table for public holidays only. 'dow' returns 0 or 6 for Sun or Sat respectively.
public holidays and sat/sun can be represented as single interval (0..24). Weekdays have to be represented by two intervals (0..8) and (18..24) hence unnest() and array[]
you can specify start date and length in generate_series() function

Based on your update to the question I added another when to case:

when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')]

The idea is to produce different interval(s) for starting date (d::date = '2012-11-14'): (0..9) and (18..24)

Allow only work time in reservations table

You need to change you table definition and add some check constraints:

CREATE TABLE schedule (
  id serial primary key,
  during tsrange not null check(
    (lower(during)::date = upper(during)::date) and 
    (date_trunc('hour', upper(during)) + INTERVAL '30 min' * ROUND(date_part('minute', upper(during)) / 30.0) = upper(during)) and
    (date_trunc('hour', lower(during)) + INTERVAL '30 min' * ROUND(date_part('minute', lower(during)) / 30.0) = lower(during)) and
    (lower(during)::time >= '8:00'::time and upper(during)::time <= '18:00'::time) and
    (date_part('dow', lower(during)) in (1,2,3,4,5) and date_part('dow', upper(during)) in (1,2,3,4,5))
  ),
  EXCLUDE USING gist (during WITH &&)
);

The checks are in this order

start and end day are the same
start / end can must be on 30 min boundary
and between 8:00 .. 18:00
only weekdays

We need something in holiday table:
insert into holiday values ('2012-11-28');

check can not reference other table hence we need trigger function (it might be better to put all check into this function i.e. have them at one place):

create function holiday_check() returns trigger language plpgsql stable as $$
begin
    if exists (select * from holiday where day in (lower(NEW.during)::date, upper(NEW.during)::date)) then
        raise exception 'public holiday';
    else
        return NEW;
    end if;
end;
$$;

Then we need to create triggers before insert/update:

create trigger holiday_check_i before insert on schedule for each row execute procedure holiday_check();
create trigger holiday_check_u before update on schedule for each row execute procedure holiday_check();

Finally, some tests:

-- OK
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:00'));
INSERT 0 1

-- out of business hours
insert into schedule(during) values (tsrange('2012-11-26 04:00', '2012-11-26 05:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (12, ["2012-11-26 04:00:00","2012-11-26 05:00:00")).

-- End time can be only 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00, 17:30 or 18:00 exclusive
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:10'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (13, ["2012-11-26 08:00:00","2012-11-26 09:10:00")).

-- Start time can be only 8:00 , 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00 or 17:30 inclusive
insert into schedule(during) values (tsrange('2012-11-26 11:24', '2012-11-26 13:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (14, ["2012-11-26 11:24:00","2012-11-26 13:00:00")).

-- holiday
insert into schedule(during) values (tsrange('2012-11-28 10:00', '2012-11-28 13:00'));
ERROR:  public holiday

How to find first free start times from reservations in Postgres

Adapted schema

CREATE EXTENSION btree_gist;
CREATE TYPE timerange AS RANGE (subtype = time);  -- create type once

-- Workers
CREATE TABLE worker(
   worker_id serial PRIMARY KEY
 , worker text NOT NULL
);
INSERT INTO worker(worker) VALUES ('JOHN'), ('MARY');

-- Holidays
CREATE TABLE pyha(pyha date PRIMARY KEY);

-- Reservations
CREATE TABLE reservat (
   reservat_id serial PRIMARY KEY
 , worker_id   int NOT NULL REFERENCES worker ON UPDATE CASCADE
 , day         date NOT NULL CHECK (EXTRACT('isodow' FROM day) < 7)
 , work_from   time NOT NULL -- including lower bound
 , work_to     time NOT NULL -- excluding upper bound
 , CHECK (work_from >= '10:00' AND work_to <= '21:00'
      AND work_to - work_from BETWEEN interval '15 min' AND interval '4 h'
      AND EXTRACT('minute' FROM work_from) IN (0, 15, 30, 45)
      AND EXTRACT('minute' FROM work_from) IN (0, 15, 30, 45)
    )
 , EXCLUDE USING gist (worker_id WITH =, day WITH =
                     , timerange(work_from, work_to) WITH &&)
);
INSERT INTO reservat (worker_id, day, work_from, work_to) VALUES 
   (1, '2014-10-28', '10:00', '11:30')  -- JOHN
 , (2, '2014-10-28', '11:30', '13:00'); -- MARY

-- Trigger for volatile checks
CREATE OR REPLACE FUNCTION holiday_check()
  RETURNS trigger AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM pyha WHERE pyha = NEW.day) THEN
      RAISE EXCEPTION 'public holiday: %', NEW.day;
   ELSIF NEW.day < now()::date OR NEW.day > now()::date + 31 THEN
      RAISE EXCEPTION 'day out of range: %', NEW.day;
   END IF;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql STABLE; -- can be "STABLE"

CREATE TRIGGER insupbef_holiday_check
BEFORE INSERT OR UPDATE ON reservat
FOR EACH ROW EXECUTE PROCEDURE holiday_check();

Major points

Don't use ~~char(n)~~. Rather varchar(n), or better yet, varchar or just text.
- Any downsides of using data type "text" for storing strings?
Don't use the name of a worker as primary key. It's not necessarily unique and can change. Use a surrogate primary key instead, best a serial. Also makes entries in reservat smaller, indexes smaller, queries faster, ...
Update: For cheaper storage (8 bytes instead of 22) and simpler handling I save start and end as time now and construct a range on the fly for the exclusion constraint:
```
EXCLUDE USING gist (worker_id WITH =, day WITH =
                  , timerange(work_from, work_to) WITH &&)
```
Since your ranges can never cross the date border by definition, it would be more efficient to have a separate date column (day in my implementation) and a time range. The type timerange is not shipped in default installations, but easily created. This way you can largely simplify your check constraints.
Use EXTRACT('isodow', ...) to simplify excluding sundays

The day of the week as Monday(1) to Sunday(7)
I assume you want to allow the upper border of '21:00'.
Borders are assumed to be including for the lower and excluding for the upper bound.
The check whether new / updated days lie within a month from "now" is not IMMUTABLE. Moved it from the CHECK constraint to the trigger - else you might run into problems with dump / restore! Details:
- Disable all constraints and table checks while restoring a dump

Aside
Besides simplifying input and check constraints I expected timerange to save 8 bytes of storage as compared to tsrange since time only occupies 4 bytes. But it turns out timerange occupies 22 bytes on disk (25 in RAM), just like tsrange (or tstzrange). So you might go with tsrange as well. The principle of query and exclusion constraint are the same.

Query

Wrapped into an SQL function for convenient parameter handling:

CREATE OR REPLACE FUNCTION f_next_free(_start timestamp, _duration interval)
  RETURNS TABLE (worker_id int, worker text, day date
               , start_time time, end_time time) AS
$func$
   SELECT w.worker_id, w.worker
        , d.d AS day
        , t.t AS start_time
        ,(t.t + _duration) AS end_time
   FROM  (
      SELECT _start::date + i AS d
      FROM   generate_series(0, 31) i
      LEFT   JOIN pyha p ON p.pyha = _start::date + i
      WHERE  p.pyha IS NULL   -- eliminate holidays
      ) d
   CROSS  JOIN (
      SELECT t::time
      FROM   generate_series (timestamp '2000-1-1 10:00'
                            , timestamp '2000-1-1 21:00' - _duration
                            , interval '15 min') t
      ) t  -- times
   CROSS  JOIN worker w
   WHERE  d.d + t.t > _start  -- rule out past timestamps
   AND    NOT EXISTS (
      SELECT 1
      FROM   reservat r
      WHERE  r.worker_id = w.worker_id
      AND    r.day = d.d
      AND    timerange(r.work_from, r.work_to) && timerange(t.t, t.t + _duration)
      )
   ORDER  BY d.d, t.t, w.worker, w.worker_id
   LIMIT  30  -- could also be parameterized
$func$ LANGUAGE sql STABLE;

Call:

SELECT * FROM f_next_free('2014-10-28 12:00'::timestamp, '1.5 h'::interval);

SQL Fiddle on Postgres 9.3 now.

Explain

The function takes a _start timestamp as minimum starting time and _duration interval. Be careful to only rule out earlier times on the starting day, not the following days. Simplest by just adding day and time: t + d > _start.

To book a reservation starting "now", just pass now()::timestamp:
```
SELECT * FROM f_next_free(`now()::timestamp`, '1.5 h'::interval);
```
Subquery d generates days starting from the input value _day. Holidays excluded.
Days are cross-joined with possible time ranges generated in subquery t.
That is cross-joined to all available workers w.
Finally eliminate all candidates that collide with existing reservations using an NOT EXISTS anti-semi-join, and in particular the overlaps operator && .

How do you do date math that ignores the year? (for date math example)
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
Calculate working hours between 2 dates in PostgreSQL

How to find first free time in reservations table in PostgreSql

Postgres 9.2 has range type and I would recommend to use them.

create table reservation (reservation tsrange);
insert into reservation values 
('[2012-11-14 09:00:00,2012-11-14 10:00:00)'), 
('[2012-11-14 10:00:00,2012-11-14 11:30:00)'), 
('[2012-11-14 12:00:00,2012-11-14 14:00:00)'), 
('[2012-11-14 16:00:00,2012-11-14 18:30:00)');

ALTER TABLE reservation ADD EXCLUDE USING gist (reservation WITH &&);

"EXCLUDE USING gist" creates index which disallows to inset overlapping entries. You can use the following query to find gaps (variant of vyegorov's query):

with gaps as (
  select 
    upper(reservation) as start, 
    lead(lower(reservation),1,upper(reservation)) over (ORDER BY reservation) - upper(reservation) as gap 
  from (
    select * 
    from reservation 
    union all values 
      ('[2012-11-14 00:00:00, 2012-11-14 08:00:00)'::tsrange), 
      ('[2012-11-14 18:00:00, 2012-11-15 00:00:00)'::tsrange)
  ) as x
) 
select * from gaps where gap > '0'::interval;

'union all values' masks out non working times hence you can make reservation between 8am and 18pm only.

Here is the result:

        start        |   gap    
---------------------+----------
 2012-11-14 08:00:00 | 01:00:00
 2012-11-14 11:30:00 | 00:30:00
 2012-11-14 14:00:00 | 02:00:00

Documentation links:
- http://www.postgresql.org/docs/9.2/static/rangetypes.html "Range Types"
- https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf

sql - how to list properties not reserved within certain date-range?

Here is the right condition:

select p.*
from properties p left outer join
     reservations r
     on p.id = r.fk_property_id and
        r.arrival < '2013-07-14 10:00:00' and
        r.departure > '2013-06-29 14:00:00'
where r.id is null

Here is the logic. First, the time conditions need to go in the on clause. In the where clause, they conflict with the left outer join.

The logic is that a room is available for all the days when there is no arrival before the later date and no departure after the first date. This takes all the various overlap possibilities into account.

The final where just finds the properties that are available because there are no reservations.

How to show clients with 0 reservations in certain year? (SQL)

You've gotten it almost right. The reason why WHERE tour.start_date BETWEEN '2022-01-01' AND '2022-12-31' returns 0 rows is because it filters out all those clients who didn't make a reservation in that period as WHERE is applied to whole result set. So, instead of adding the date condition in the WHERE clause, I'd suggest adding it in the join condition for tour. Moreover I believe an OUTER JOIN wouldn't be required here either as you just want all the clients so, a LEFT JOIN should be sufficient. I think the following should work:

SELECT client_data.name, reservation.id, COUNT(reservation.id)
FROM client_data
LEFT JOIN reservation ON client_data.id = reservation.client_id
LEFT JOIN tour ON tour.id = reservation.tour_id and tour.start_date BETWEEN '2022-01-01' AND '2022-12-31'
WHERE reservation.id IS NULL
GROUP BY client_data.name, reservation.id;

Hope it helps

Edit

As OP mentioned the above query doesn't work as intended, I think we'll have to resort to using a subquery (or cte) here which I previously wanted to avoid due to performance reasons but maybe we're getting too ahead of ourselves on that. It's possible we can avoid it but I can't think of the correct way at the moment so here's a solution with subquery that will hopefully work.

select * from client_data where id not in (
    select distinct client_id from reservation r 
    join tour t on r.tour_id = t.id
    where t.start_date BETWEEN '2022-01-01' AND '2022-12-31'
);

In this we first find out the client_ids that did make a reservation in the said time frame and filter them out from the client data.

Have attached a fiddle in which you can play around it a bit

How to Return Only Work Time from Reservations in Postgresql