如何从Postgres的保留中找到第一个自由启动时间[英] How to find first free start times from reservations in Postgres

本文是小编为大家收集整理的关于如何从Postgres的保留中找到第一个自由启动时间的处理方法,想解了如何从Postgres的保留中找到第一个自由启动时间的问题怎么解决?如何从Postgres的保留中找到第一个自由启动时间问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

人们从上午 10:00 到晚上 21:00 工作,周日和公众假期除外.

每隔 15 分钟为他们保留一次工作.工作时间从 15 分钟到 4 小时不等.整个工作必须适合一天.

如何从当前日期和时间开始在 Postgres 9.3 中查找未保留给定持续时间的第一个最近的免费开始时间?

例如,玛丽已经预订了 12:30 .. 16:00 和约翰已经预订了 12:00 到 13:00

Reservat 表包含预订,yksus2 表包含作品和pyha 表包含公共假期.表结构如下.如果有帮助,可以更改预留结构.

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

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

我尝试根据 如何仅从 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 等.
如何获得正确的首次预订?

返回错误结果的查询是:

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

架构:

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);

还有 发布到 pgsql-general 邮件列表.

推荐答案

适配架构

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();

要点

  • 不要使用 char(n).而是 varchar(n),或者更好的是,varchar 或者只是 text.

  • 不要使用工作人员的名字作为主键.它不一定是唯一的,可以改变.请改用代理主键,最好使用 serial.还使 reservat 中的条目更小,索引更小,查询更快,...

  • 更新: 为了更便宜的存储(8 个字节而不是 22 个字节)和更简单的处理,我现在将 start 和 end 保存为 time 并为排除约束动态构建一个范围:

    EXCLUDE USING gist (worker_id WITH =, day WITH =
                      , timerange(work_from, work_to) WITH &&)
    
  • 由于根据定义,您的范围永远不会跨越日期边界,因此拥有一个单独的 date 列(在我的实现中为 day)和一个 时间范围.类型 timerange 在默认安装中不提供,但很容易 这样可以大大简化检查约束.

  • 使用 EXTRACT('isodow', ...)简化不包括星期日

    <块引用>

    星期一(1) 到星期日(7) 是星期几

  • 我假设你想允许'21:00'的上边界.

  • 假设下界包含边框,上限则不包含边框.

  • 检查新/更新天数是否在"现在"一个月内不是IMMUTABLE.将其从 CHECK 约束移至触发器 - 否则您可能会遇到转储/恢复问题!详情:

旁白
除了简化输入和检查约束之外,我希望 timerange 比 tsrange 节省 8 个字节的存储空间,因为 time 只占用 4 个字节.但事实证明 timerange 占用磁盘上的 22 个字节(RAM 中的 25 个字节),就像 tsrange(或 tstzrange)一样.所以你也可以选择 tsrange .查询和排除约束的原理是一样的.

查询

包装成一个SQL函数方便参数处理:

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;

呼叫:

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

SQL Fiddle 现在在 Postgres 9.3 上.

解释

  • 该函数以 _start timestamp 作为最短开始时间,_duration interval.请注意仅排除开始日的较早时间,而不是接下来的几天.最简单的方法是添加日期和时间:t + d > _start.
    要从"现在"开始预订,只需通过 now()::timestamp:

    SELECT * FROM f_next_free(`now()::timestamp`, '1.5 h'::interval);
    
  • 子查询 d 从输入值 _day 开始生成天数.节假日除外.

  • 天与子查询 t 中生成的可能时间范围交叉连接.
  • 这将交叉连接到所有可用的工作人员w.
  • 最后使用 NOT EXISTS 反半连接消除所有与现有预留冲突的候选者,尤其是重叠运算符 &&.

相关:

本文地址:https://www.itbaoku.cn/post/1763821.html