如何从Postgres的预订中查找首次免费开始时间 [英] How to find first free start times from reservations in Postgres

问题描述

人们从10:00 AM到21:00 PM工作，除了星期日和公共假期。

为他们保留的工作间隔为15分钟。作业时间为15分钟至4小时。整个工作必须适合一天。

如何查找从当前日期和时间开始在Postgres 9.3中没有为给定时间保留的最接近的免费开始时间？ p>

例如，玛丽已经在12:30 .. 16:00预订了房间，约翰
约翰已经在12:00到13:00预订了房间

Reservat表包含预订，yksus2表包含workes，
pyha表包含公共假期。表结构如下。如果有帮助的话，可以更改储备结构。

查询1.5小时持续时间的最开始时间应该返回

  John 2014-10-28 10:00 
玛丽2014-10-28 10:00 
约翰2014-10-28 10:15 
玛丽2014 -10-28 10:15 
约翰2014-10-28 10:30 
玛丽2014-10-28 10:30 
玛丽2014-10-28 11:00 
约翰2014-10-28 13:00 
玛丽2014-10-28 16:00 
玛丽2014-10-28 16:15 
玛丽2014-10-28 16:30 
 ...等，也从第二天开始
  

我尝试根据如何仅从PostgreSql中的保留中返回工作时间？下方，但返回错误结果：

  MARY 2014-10-28 13:00:00 
 MARY 2014- 10-29 22：34：40.850255 
 JOHN 2014-10-30 22：34：40.850255 
 MARY 2014-10-31 22：34：40.850255 
 MARY 2014-11-03 22： 34：40.850255 
  

也不会返回开始时间10：00、10：30等。

如何获得适当的首次预订？

返回错误结果的查询是：

 插入保留区（objekt2，期间）值
（'MARY'，'[2014-10-28 11：30：00,2014-10-28 13:00:00）'），
（'JOHN'，'[ 2014-10-28 10：00：00,2014-10-28 11:30:00）'）; 
 
的差距为（
选择
 yksus，
较高（期间）作为开始，
领先（较低（期间），1，较高（期间） ））超过（在期间订购）-从（
选择
 yksus2.yksus的间隙
，在
的
期间从储备金中加入yksus2在储备金上。 objekt2 = yksus2.yksus 
其中upper（during）> = current_date 
 union all 
 select 
 yksus2.yksus，
 unnest（pyha时
不为null时，然后array [tsrange1（d，d +间隔'1天'）] 
当date_part（'dow'，d）在（0，6）中时，然后array [tsrange1（d，d +间隔'1 day']] 
当d :: date = current_Date然后数组[
 tsrange1（d，current_timestamp），
 tsrange1（d +间隔'20小时'，d +间隔'1天' ）] 
 else array [tsrange1（d，d + interval'8 hours'），
 tsrange1（d +间隔 20小时，d +间隔 1天）] 
 end）
来自yksus2，generate_series（
 current_timestamp，
 current_timestamp +间隔'1 month'，
间隔'1天'
）为s（d）
，在pyha上加入pyha = d :: date 
）为x 
）
 
选择yksus，从差距
开始
，其中gap> = interval'1hour 30 minutes'
 order by start 
 limit 30 
  

模式：

 创建扩展btree_gist; 
 CREATE TABLE Reservat（
 id串行主键，
 objekt2 char（10）不为空null在更新级联可推迟时引用yksus2，tsrange期间的
不为空检查（
低（期间）::日期=当前日期和当前日期之间的较高（期间）::日期
和较低（期间）+间隔'1个月'
 
和（较低（期间）::时间> ; = '10：00':: time and upper（during）:: time< '21：00':: time）
 AND EXTRACT（MINUTE FROM lower（during））IN（0，15，30 ，45）
和EXTRACT（MINUTE FROM upper（during））IN（0，15，30，45）
和（date_part（'dow'，lower（during））在（1,2， 3,4,5,6）
和date_part（'dow'，upper（during））in（1,2,3,4,5,6））
），
 
不使用gist（objekt2 WITH =，在WITH&&期间）
）； 
 
创建或替换函数holiday_check（）返回触发语言plpgsql稳定，因为$$ 
如果存在则从
开始（从pyha中选择*，其中pyha在（lower（NEW.during）： ：date，upper（NEW.during）:: date））然后
引发异常'public holiday％'，lower（NEW.during）; 
否则
返回NEW； 
结尾，如果； 
结尾； 
 $$; 
 
在为每行插入或更新Reservat之前创建触发器holiday_check_i，执行过程holiday_check（）; 
 
创建或替换功能public.tsrange1（开始时标带时区，
完成时标带时区）返回tsrange AS 
 $ BODY $ 
选择tsrange（开始::不带时区的时间戳，完成::不带时区的时间戳）; 
 $ BODY $语言sql不可变； 
 
 
-工人
创建表yksus2（yksus char（10）主键）; 
插入yksus2值（'JOHN'），（'MARY'）; 
 
-公共假期
创建表pyha（pyha日期主键）; 
  

也邮件列表> low b $ b

解决方案

自适应模式

 创建扩展btree_gist; 
 CREATE TYPE timerange AS RANGE（子类型=时间）； -一次创建类型
 
-Workers 
 CREATE TABLE worker（
 worker_id串行主键
，工作者文本NOT NULL 
）; 
插入工人（工人）价值观（ JOHN），（ MARY）； 
 
-假期
创建表pyha（pyha date PRIMARY KEY）; 
 
-保留
 CREATE TABLE reservat（
 reservat_id序列主键
，worker_id int NOT NULL引用更新级联上的worker 
，日日期NOT NULL CHECK（EXTRACT（'isodow'FROM day）< 7）
，work_from time NOT NULL-包括下限
，work_to time NOT NULL-不包括上限
，CHECK（work_from > = '10：00'AND work_to< = '21：00'
 AND work_to-work_from间隔'15 min'与间隔'4 h'
 AND EXTRACT（'minute'FROM work_from ）IN（0，15，30，45）
 AND EXTRACT（'from'work_from）IN（0，15，30，45）
）
，使用gist（worker_id WITH =，day WITH = 
，timerange（work_from，work_to）WITH&）
）; 
插入到保留中（worker_id，day，work_from，work_to）值
（1，'2014-10-28'，'10：00'，'11：30'）-JOHN 
，（2，'2014-10-28'，'11：30'，'13：00'）; -MARY 
 
-触发易失性支票
创建或替换功能holiday_check（）
 RETURNS AS 
 $ func $ 
 BEGIN 
如果存在（从pyha中选择1，其中pyha = NEW.day），然后
升空'公共假期：％'，NEW.day； 
 ELSIF NEW.day< now（）:: date或NEW.day> now（）:: date + 31 THEN 
超出天数的超出天数：％，NEW.day； 
 END IF; 
 
返回新的； 
 END 
 $ func $语言plpgsql稳定; -可以是稳定的 
 
创建触发器insupbef_holiday_check 
在插入或更新保留行
之前，为每个行执行程序holiday_check（）; 
  

要点

• 不要使用 char（n） 。宁可 varchar（n），或者更好的是 varchar 或仅 text 。

  
  
  
  
  
  • 使用数据类型文本的任何弊端
  
  



• 不要使用工人的名字作为主键。它不一定是唯一的，并且可以更改。最好使用代理主键，最好使用 serial 。还可以使储备金中的条目更小，索引更小，查询更快，...




• 更新：为了更便宜的存储（从8个字节而不是22个字节）和更简单的处理，我现在将开始和结束保存为 time 并动态构建一个排除范围约束：

   排除要点（worker_id WITH =，day WITH = 
  ，timerange（work_from，work_to）WITH& ;&）
    




• 因为您的范围不能从未跨越日期边界 strong，最好有一个单独的 date 列（在我的实现中为 day ）和一个时间范围。 类型时间范围未在默认安装中提供，但很容易创建。这样，您可以在很大程度上简化检查约束。




• 使用 EXTRACT（'isodow'，.. 。）以简化排除星期日的时间

  
  

  星期几为星期一（1）至星期日（7）

  
  




• 我假设您想 allow '21的上边界： 00'。




• 边界被认为包括上下边界，不包括上限。




• 检查新的或更新的日期是否位于距 now一个月内的日期，不是 IMMUTABLE 。将其从 CHECK 约束移至触发器-否则您可能会遇到转储/还原问题！详细信息：

  
  
  
  
  
  • 在还原转储时禁用所有约束和表检查
  
  

在旁边

除了简化输入和检查约束，我还期望时间范围 tsrange 相比，c $ c>可节省8个字节的存储空间，因为时间仅占用4个字节。但事实证明，时间范围在磁盘上占用22个字节（RAM中为25个字节），就像 tsrange （或 tstzrange ）。因此，您也可以使用 tsrange 。查询和排除约束的原理相同。

查询

包装到SQL函数中以方便使用参数处理：

 创建或替换功能f_next_free（_start时间戳，_duration间隔）
返回表（worker_id int，工作者文本，日期日期
，开始时间，结束时间）AS 
 $ func $ 
 SELECT w.worker_id，w.worker 
，dd AS day 
，tt AS start_time 
，（tt + _duration）作为结束时间
 FROM（
选择_start :: date + i AS d 
 FROM generate_series（0，31）i 
左联接pyha p ON p.pyha = _start :: date + i 
其中p.pyha为NULL-消除假期
）d 
交叉加入（
选择t :: time 
 FROM generate_series（时间戳'2000-1-1 10:00'
，时间戳'2000-1-1 21:00'-_duration 
，间隔‘15分钟’）t 
）t-乘以
交叉加入工人w 
而d.d + t.t> _start-排除过去的时间戳
并且不存在（
从保留r 
中选择1 
 WHERE r.worker_id = w.worker_id 
 AND r.day = dd 
 AND timerange（r.work_from，r.work_to）&& timerange（tt，tt + _duration）
）
按dd，tt，w.worker，w.worker_id $订购b $ b LIMIT 30-也可以参数化
 $ func $ LANGUAGE sql STABLE; 
  

致电：

  SELECT * FROM f_next_free（'2014-10-28 12:00':: timestamp，'1.5 h':: interval）; 
  

现在在Postgres 9.3上 SQL Fiddle 。

说明

• 该函数需要 _start 时间戳 作为最短开始时间和 _duration间隔。请注意，仅排除在开始当天的较早时间，而不是随后的几天。只需添加日期和时间即可最简单： t + d> _start 。
  
  要预订从现在开始的预订，只需传递 now（）:: timestamp ：

    SELECT * FROM f_next_free（`now（）:: timestamp`，'1.5 h':: interval）; 
    




• 子查询 d 生成的日期开始从输入值 _day 开始。



• 天与子查询 t 中生成的可能时间范围交叉连接。


• 与所有可用工人 w 交叉连接。


• 最后消除所有与现有保留冲突的候选人使用 NOT EXISTS 反半联接，尤其是重叠运算符&& 。

相关：

• 如何处理忽略年份的日期数学？（对于日期数学示例）


• 在PostgreSQL中使用EXCLUDE防止相邻/重叠的条目


• 计算PostgreSQL中两个日期之间的工作时间

People work from 10:00AM to 21:00PM except Sundays and public holidays.

Jobs for them are reserved at 15 minute intervals. Job duration is from 15 minutes to 4 hours. Whole job must fit to single day.

How to find first nearest free start times which are not reserved for given duration in Postgres 9.3 starting from current date and time ?

For example, Mary has already reservation at 12:30 .. 16:00 and John has already reservation at 12:00 to 13:00

Reservat table contains reservations, yksus2 table contains workes and pyha table contains public holidays. Table structures are below. Reservat structure can changed if this helps.

Query for ealiest start times for duration of 1.5 hours should return

John 2014-10-28 10:00
Mary 2014-10-28 10:00
John 2014-10-28 10:15
Mary 2014-10-28 10:15
John 2014-10-28 10:30
Mary 2014-10-28 10:30
Mary 2014-10-28 11:00
John 2014-10-28 13:00
Mary 2014-10-28 16:00
Mary 2014-10-28 16:15
Mary 2014-10-28 16:30
... etc and also starting from next days

I tried query based on answer in How to return only work time from reservations in PostgreSql? below but it returns wrong result:

MARY  2014-10-28 13:00:00
MARY  2014-10-29 22:34:40.850255
JOHN  2014-10-30 22:34:40.850255
MARY  2014-10-31 22:34:40.850255
MARY  2014-11-03 22:34:40.850255

Also sliding start times 10:00, 10:30 etc are not returned.
How to get proper first reservations?

Query which returns wrong result is:

insert into reservat (objekt2, during) values 
('MARY', '[2014-10-28 11:30:00,2014-10-28 13:00:00)'), 
('JOHN', '[2014-10-28 10:00:00,2014-10-28 11:30:00)');

with gaps as (
    select
        yksus, 
        upper(during) as start,
        lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap
    from (
        select 
           yksus2.yksus,
           during
          from reservat join yksus2 on reservat.objekt2=yksus2.yksus 
          where  upper(during)>= current_date
        union all
        select
            yksus2.yksus,
            unnest(case
                when pyha is not null then array[tsrange1(d, d + interval '1 day')]
                when date_part('dow', d) in (0, 6) then array[tsrange1(d, d + interval '1 day')]
                when d::date =  current_Date then array[
                            tsrange1(d, current_timestamp ), 
                            tsrange1(d + interval '20 hours', d + interval '1 day')]
                else array[tsrange1(d, d + interval '8 hours'), 
                           tsrange1(d + interval '20 hours', d + interval '1 day')]
            end)
        from yksus2, generate_series(
            current_timestamp,
            current_timestamp + interval '1 month',
            interval '1 day'
        ) as s(d) 
        left join pyha on pyha = d::date
    ) as x 
)

select yksus, start
  from gaps 
where gap >= interval'1hour 30 minutes'
order by start
limit 30

Schema:

CREATE EXTENSION btree_gist;
CREATE TABLE Reservat (
      id serial primary key,
      objekt2 char(10) not null references yksus2 on update cascade deferrable,
      during tsrange not null check(
         lower(during)::date = upper(during)::date
         and lower(during) between current_date and current_date+ interval'1 month'

         and (lower(during)::time >= '10:00'::time and upper(during)::time < '21:00'::time) 
         AND EXTRACT(MINUTE FROM lower(during)) IN (0, 15, 30,45)
         AND EXTRACT(MINUTE FROM upper(during)) IN (0, 15, 30, 45)
         and (date_part('dow', lower(during)) in (1,2,3,4,5,6) 
         and date_part('dow', upper(during)) in (1,2,3,4,5,6)) 
      ),

      EXCLUDE USING gist (objekt2 WITH =, during WITH &&)
    );  

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

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

CREATE OR REPLACE FUNCTION public.tsrange1(start timestamp with time zone,
    finish timestamp with time zone ) RETURNS tsrange AS
$BODY$
SELECT tsrange(start::timestamp without time zone, finish::timestamp without time zone );
$BODY$ language sql immutable;


-- Workers
create table yksus2( yksus char(10) primary key);
insert into yksus2 values ('JOHN'), ('MARY');

-- public holidays
create table pyha( pyha date primary key);

Also posted to the pgsql-general mailing list.

解决方案

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 && .

Related:

• 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

这篇关于如何从Postgres的预订中查找首次免费开始时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！