- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
从 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:在下面的示例中)它还会发现星期六、星期日和公共(public)假期。公众假期在表中定义
create table pyha ( pyha date primary key)
如何同时排除周末和公共(public)假期?
保留硬编码空闲时间以进行查询
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 gaps
where gap > '0'::interval
order 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,
lead(lower(during),1,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 gaps
where gap > '0'::interval
order by start
更新2
我尝试了更新的答案,但它返回了错误的数据。完整的测试用例是:
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,
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 = 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')]
else array[tsrange(d, d + interval '8 hours'),
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,
date_part('epoch', gap) / (60*60) as tunde
from gaps
where gap > '0'::interval
order by start
观察到的第一行:
"2012-11-19 11:30:00"
预期:
"2012-11-19 12:00:00"
如何修复?
最佳答案
您可以使用 generate_series() 函数来屏蔽非营业时间:
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
让我解释一些棘手的部分:
pyha
表中,因为您可以使用 date_part('dow', d)
函数。仅在公共(public)假期使用 pyha
表。 'dow' 分别为星期日或星期六返回 0 或 6。根据您对问题的更新,我在 case
中添加了另一个 when
:
when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')]
想法是为开始日期 (d::date = '2012-11-14'
) 生成不同的时间间隔:(0..9) 和 (18..24 )
关于sql - 如何仅从 PostgreSql 中的预订返回工作时间?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13433863/
我是一名优秀的程序员,十分优秀!