程序员经验分享-hwhale

如何从 Postgres 的预订中查找第一个免费开始时间

2024-01-08

人们的工作时间为上午 10:00 至晚上 21:00（周日和公共假期除外）。

每隔 15 分钟为他们预留一个工作岗位。工作时间为15分钟至4小时。整个工作必须适合一天。

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

例如，玛丽已经预订了 12:30 .. 16:00 并且 John 已预订 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 中的预订中仅返回工作时间？ https://stackoverflow.com/questions/13433863/how-to-return-only-work-time-from-reservations-in-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

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 发布到 pgsql-general 邮件列表 http://www.postgresql.org/message-id/flat/CAA-aLv6NDTUwV=P3z_aO88cwqLUfTdb8ttS9rjgrQYby7pyJaQ@mail.gmail.com#CAA-aLv6NDTUwV=P3z_aO88cwqLUfTdb8ttS9rjgrQYby7pyJaQ@mail.gmail.com.


适应模式

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.

    • 使用数据类型“文本”存储字符串有什么缺点吗？ https://stackoverflow.com/questions/20326892/any-downsides-of-using-data-type-text-for-storing-strings/20334221#20334221

  • 不要使用工人的姓名作为主键。它不一定是唯一的并且可以改变。使用代理主键代替，最好serial。还创建条目reservat更小，索引更小，查询更快，...

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

    EXCLUDE USING gist (worker_id WITH =, day WITH =
                  , timerange(work_from, work_to) WITH &&)

  • 因为你的范围可以永远不要跨越日期边界根据定义，拥有一个单独的系统会更有效date柱子 （day在我的实现中）和时间范围. 方式timerange默认安装中不提供，但可以轻松创建。 http://www.postgresql.org/docs/current/interactive/rangetypes.html#RANGETYPES-DEFINING这样您就可以很大程度上简化您的检查约束。

  • Use EXTRACT('isodow', ...)简化排除星期日 http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

    一周中的某一天为星期一 (1) 到星期日 (7)

  • 我假设你想要allow'21:00' 的上边框。

  • 假设边界包括下限，不包括上限。

  • 检查新的/更新的日期是否在“现在”之后的一个月内不是IMMUTABLE。将其从CHECK对触发器的约束 - 否则你可能会遇到转储/恢复问题！细节：

    • 恢复转储时禁用所有约束和表检查 https://dba.stackexchange.com/questions/75613/disable-all-constraints-and-table-checks-while-restoring-a-dump/75635#75635

Aside
除了简化输入和检查约束之外，我期望timerange相比之下，节省 8 个字节的存储空间tsrange since time仅占用4个字节。但事实证明timerange在磁盘上占用 22 个字节（在 RAM 中占用 25 个字节），就像tsrange (or tstzrange）。所以你可能会选择tsrange以及。查询和排除约束的原理是一样的。

Query

包装到 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;

Call:

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

SQL小提琴 http://sqlfiddle.com/#!15/1526b/1现在在 Postgres 9.3 上。

Explain

  • 该函数需要一个_start timestamp作为最短开始时间和_duration interval。请小心，只排除较早的时间starting当天，而不是接下来的几天。最简单的方法是添加日期和时间：t + d > _start.
    要从“现在”开始预订，只需通过now()::timestamp:

    SELECT * FROM f_next_free(`now()::timestamp`, '1.5 h'::interval);

  • 子查询d从输入值开始生成天数_day。节假日除外。

  • 天数与子查询中生成的可能时间范围交叉连接t.
  • 这是交叉连接到所有可用的工人w.
  • 最后使用以下方法消除与现有保留冲突的所有候选者NOT EXISTS反半连接，特别是重叠运算符&& .

Related:

  • 如何进行忽略年份的日期数学？ https://stackoverflow.com/questions/15169410/how-do-you-do-date-math-that-ignores-the-year/15179731#15179731（对于日期数学示例）
  • 在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目 https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869
  • 计算 PostgreSQL 中 2 个日期之间的工作时间 https://stackoverflow.com/questions/1839319/calculate-working-hours-between-2-dates-in-postgresql/17282639#17282639
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

postgresql

range

SCHEDULE

如何从 Postgres 的预订中查找第一个免费开始时间 的相关文章

随机推荐

热门标签