如何从PostgreSql中的预约返回工作时间？

概述从 How to find first free time in reservations table in PostgreSql中选择最佳答案 create table reservation (during tsrange, EXCLUDE USING gist (during WITH &&) ); 用于查找从给定日期和时间开始的时间表中的差距(2012-11-17 8：在下面的示例中) 从 How to find first free time in reservations table in PostgreSql中选择最佳答案

create table reservation (during tsrange,EXCLUDE USING gist (during WITH &&) );

用于查找从给定日期和时间开始的时间表中的差距(2012-11-17 8：在下面的示例中)
它也发现了周六,周日和公众假期.
公共假期在表格中定义

create table pyha ( pyha date primary key)

如何排除周末和公众假期？

硬编码空闲时间作为保留查询之类的

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 values     ('(,2012-11-17 8:)'::tsrange),-- given date and hour from which to find free work time     ('[2012-11-17 0:,2012-11-18 24:)'::tsrange),-- exclude saturday     ('[2012-11-18 0:,2012-11-19 8:)'::tsrange),-- exclude sunday     ('[2012-11-19 18:,2012-11-20 8:)'::tsrange),('[2012-11-20 18:,2012-11-21 8:)'::tsrange),('[2012-11-21 18:,2012-11-22 8:)'::tsrange),('[2012-11-22 18:,2012-11-23 8:)'::tsrange),('[2012-11-23 18:,2012-11-24 24:)'::tsrange),('[2012-11-24 0:,2012-11-25 24:)'::tsrange),-- exclude saturday     ('[2012-11-25 0:,2012-11-26 8:)'::tsrange)  -- exclude sunday ) as x)select *  from gapswhere gap > '0'::intervalorder by start

每个空闲时间范围都需要单独的联合行.

从给定的日期和时间开始,哪个是在工作日和工作时间(8：00~18：00)返回空闲时间的最佳方法？

更新

选择回答始终在8:00返回空闲时间.
如果在指定的开始日期之前不是在指定的开始时间之前返回空闲时间,例如,如果开始时间是9,则不是在2012-11-19 9:00之前返回？
开始时间可能只有值8,9,10,11,12,13,14,15,16或17

即使2012-11-19 8:00如果免费也应该返回2012-11-19 9:00.
只有在2012-11-19 9:00没有空闲时间,并且在接下来的工作日8:00首次免费时,它应该返回8:00.

我尝试通过将2012-11-19 9：添加到两个地方来解决这个问题,如下面的查询所示,但此查询仍然会在2012-11-19 8:00返回空闲时间.
如何解决这个问题,以便在2012-11-19 9:00返回空闲时间？

create table reservation (during tsrange,EXCLUDE USING gist (during WITH &&) );create table pyha ( pyha date primary key);with gaps as (    select        upper(during) as start,upper(during)) over (ORDER BY during) - upper(during) as gap    from (        select during          from reservation             where upper(during)>= '2012-11-19 9:'       union all values         ('(,2012-11-19 9:)'::tsrange)        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')]                else array[tsrange(d,d + interval '8 hours'),tsrange(d + interval '18 hours',d + interval '1 day')]            end)        from generate_serIEs(            '2012-11-19'::timestamp without time zone,'2012-11-19'::timestamp without time zone+ interval '3 month',interval '1 day'        ) as s(d)        left join pyha on pyha = d::date    ) as x)select start,date_part('epoch',gap) / (60*60) as hours  from gapswhere gap > '0'::intervalorder by start

UPDATE2

我尝试了更新的答案,但它返回错误的数据.完整的测试用例是：

create temp table reservation  ( during tsrange ) on commit drop;insert into reservation values('[2012-11-19 11:00:00,2012-11-19 11:30:00)'::tsrange );with gaps as (    select        upper(during) as start,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 d::date =  DATE'2012-11-19' then array[                            tsrange(d,'2012-11-19 12:'),-- must return starting at 12:00                            tsrange(d + interval '18 hours',d + interval '1 day')]            end)        from generate_serIEs(            DATE'2012-11-19'::timestamp without time zone,DATE'2012-11-19'::timestamp without time zone+ interval '3 month',interval '1 day'        ) as s(d)         left join pyha on pyha = d::date    ) as x )select start,gap) / (60*60) as tunde  from gaps where gap > '0'::intervalorder by start

观察第一行：

"2012-11-19 11:30:00"

预期：

"2012-11-19 12:00:00"

怎么修 ？

您可以使用generate_serIEs()函数来屏蔽非工作时间：

with gaps as (    select        upper(during) as start,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 d::date = '2012-11-14' then array[tsrange(d,d + interval '9 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 gapswhere gap > '0'::intervalorder by start

让我解释一些棘手的部分：

>你不必将sat / sun的日期插入到pyha表中,因为你可以使用date_part(‘dow’,d)函数.仅在公众假期使用pyha表.对于太阳或周六,’dow’分别返回0或6.
>公共假期和sat / sun可以表示为单个间隔(0..24).工作日必须由两个间隔(0..8)和(18..24)表示,因此unfst()和array []
>您可以在generate_serIEs()函数中指定开始日期和长度

根据您对问题的更新,我添加了另一个案例：

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

我们的想法是为开始日期生成不同的间隔(d :: date =’2012-11-14′):( 0..9)和(18..24)

总结

以上是内存溢出为你收集整理的如何从PostgreSql中的预约返回工作时间？全部内容，希望文章能够帮你解决如何从PostgreSql中的预约返回工作时间？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。