使用生成序列生成用于预订约会的时隙

Question

我正在尝试生成一个可用的时隙函数。 每天我都在捕捉开始时间和结束时间。

for eg: start time is 08:00 AM and end time is 03:00 PM.

我有另一张表可以捕获当天的休息时间，

for eg: 11:00 AM to 11:15 AM and 01:00 PM to 1:30 PM.

在另一个表中，我正在存储已经预订的插槽

for eg: 
09:00 AM to 09:30 AM
11:15 AM to 11:45 AM
01:30 PM 02:00PM

我正在使用generate_series来生成开始和结束时间之间的时间间隔。

select * FROM generate_series(
timestamp '2016-11-09 08:00 AM',
timestamp '2016-11-09 03:00 PM',
INTERVAL '30m'
) t

我需要从这个生成的系列中排除休息时间和已经预订的时间。我尝试使用除功能外，但此插槽上午 11：15 至上午 11：45 无法删除。 我通常如何编写条件来删除它？

查询除外

SELECT t
FROM generate_series(
timestamp  '2016-11-09 08:00 AM',
timestamp  '2016-11-09 03:00 PM',
INTERVAL '1 hour'
) t
EXCEPT
SELECT concat(start_date,' ' , start_time)::timestamp as booked 
FROM my_table where start_date::date = '2016-11-09';

my_table详情

CREATE TABLE public.my_table
(
id bigint NOT NULL,
start_date character varying(500),
start_time character varying(500),
CONSTRAINT pk_my_table PRIMARY KEY (id))
WITH (
OIDS=FALSE
);

Answer 1

以下工作，我建议你使用较小的间隔，大概 15 分钟：

with my_table as (
select '09:00 AM' start_time , '2016-11-09'::character varying start_date
)
SELECT *
FROM generate_series(
timestamp  '2016-11-09 08:00 AM',
timestamp  '2016-11-09 03:00 PM',
INTERVAL '15 minutes'
) t
EXCEPT
SELECT concat(start_date,' ' , start_time)::timestamp as booked 
FROM my_table where start_date::date = '2016-11-09'

但老实说，我根本不会将约会存储为看起来像日期的字符串或看起来像一天中的时间的字符串。字符 (500( 列会很乐意接受诸如'this is not a date or a time'之类的数据，突然间您有一个错误，因为它不会转换。或者数据中可能存在30/02/2019等日期，并且很难找到。

为什么不将约会开始存储为时间戳？这将更加高效。例如

with my_table as (
select '2016-11-09 09:00:00'::timestamp start_date
)

SELECT *
FROM generate_series(
timestamp  '2016-11-09 08:00 AM',
timestamp  '2016-11-09 03:00 PM',
INTERVAL '15 minutes'
) t
EXCEPT
SELECT start_date
FROM my_table 
where start_date >= '2016-11-09' and start_date < '2016-11-10'

此类查询将利用该start_date列上的索引来筛选数据。

请不要使用字符串来存储日期或时间。我也不会将日期与一天中的时间分开。