Генерировать ряды, используя время перерыва

У меня есть таблица, в которой указаны часы работы магазина и часы закрытия

    CREATE TABLE public.open_hours
(
  id bigint NOT NULL,
  open_hour character varying(255),
  end_hour character varying(255),
  day character varying(255),
  CONSTRAINT pk_open_hour_id PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.open_hours
  OWNER TO postgres;

У меня есть еще один стол, который

CREATE TABLE public.break_hours
(
id bigint ,
start_time character varying(255),
end_time character varying(255),
open_hour_id bigint ,
CONSTRAINT break_hours_pkey PRIMARY KEY (id),
 CONSTRAINT fkinhl5x01pnn54nv15ol5ntxr5 FOREIGN KEY (open_hour_id )
  REFERENCES public.open_hours(id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.break_hours
OWNER TO postgres;

Мне нужно создать временной ряд с 30-минутным интервалом на основе времени перерыва.

Например: если мои часы работы - 08:00, а час окончания - 18:00, время перерыва - с 11:00 до 11:30, а другое время перерыва - с 15:00 до 15:15, тогда мне нужно для создания серий с 08:00 до 11:00, с 11:30 до 15:00 и с 15:15 до 18:00.

образец данных

open_hours
-----------
id              open_hours                  end_hour    day
 1              08:00 AM                    06:00 PM    Monday

break_hours


id        start_time   end_time   open_hour_id
 1        11:00 AM     11:30 AM    1
 2        03:00 PM     03:15 PM    1

Sample out put
--------------
08:00 AM
08:30 AM
09:00 AM
09:30 AM
10:00 AM
10:30 AM
11:30 AM
12:00 PM
12:30 PM
01:00 PM
01:30 PM
02:PM PM
02:30 PM
03:15 PM
03:45 PM
04:15 PM
04:45 PM
05:15 PM

Query used for generating series between open hours is
SELECT DISTINCT gs AS start_time,gs + interval '30min' as end_time 
                     FROM   generate_series( timestamp '2018-11-09 08:00 AM', timestamp '2018-11-09 06:00 PM', interval '30min' )gs 
                     ORDER BY start_time

Как ваша вторая таблица связана с первой? Время и даты - это текст во второй таблице. Как выглядят их ценности? Что вы имеете в виду под «30-минутными интервалами» - в вашем примере я не вижу 30-минутных интервалов. Добавьте образцы данных и ожидаемый результат.

S-Man 26.10.2018 08:52

Где дата в open_hours?

S-Man 26.10.2018 09:03

@ S-Man Спасибо за ваш быстрый ответ, я обновил свой вопрос, добавив более подробную информацию. Я пытаюсь создать систему бронирования. Поэтому мне нужно указать доступные 30-минутные интервалы, используя часы перерыва и час начала и окончания.

Shamseer Pc 26.10.2018 09:17

@ S-Man В часы работы я сохраняю день недели, например понедельник, вторник и т. д., И дату, которую я планирую передать в процедуру в качестве входного параметра. Поэтому в зависимости от дня даты я получу часы работы детали из таблицы часов работы

Shamseer Pc 26.10.2018 09:23
1
4
74
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Похоже, вам нужно очистить вашу табличную лепку. Например. вам следует хранить время не как текстовые типы, а как time without time zone.


демонстрация: db <> рабочий пример
WITH hours AS (
    SELECT 
        oh.open_hour + '1970-01-01'::date as open_hour, 
        oh.end_hour + '1970-01-01'::date as end_hour, 
        bh.start_time + '1970-01-01'::date as break_start,
        bh.end_time + '1970-01-01'::date as break_end,
        lead(start_time + '1970-01-01'::date) OVER (ORDER BY start_time) as next_start_time
    FROM open_hours oh
    LEFT JOIN break_hours bh
    ON oh.id = bh.start_date
)
SELECT generate_series(open_hour, break_start, interval '30 minutes')::time as time_slot 
FROM (
    SELECT 
        open_hour, break_start
    FROM hours
    ORDER BY break_start
    LIMIT 1
)s

UNION 

SELECT 
    generate_series(break_end, next_start_time, interval '30 minutes')::time
FROM ( 
    SELECT 
        break_end, next_start_time
    FROM
        hours
    WHERE next_start_time IS NOT NULL
) s

UNION

SELECT generate_series(break_end, end_hour, interval '30 minutes')::time 
FROM (
    SELECT 
        break_end, end_hour
    FROM hours
    ORDER BY break_start DESC
    LIMIT 1
) s

Объяснение:

Пункт WITH (CTE):

Объединение обеих таблиц. Я добавляю бессмысленную дату, потому что это приводит к timestamp. Используемая позже функция generate_series работает только для timestamp, но не для типа time. Деталь вырезается позже, после генерации с помощью литья ::time.

Результат CTE:

open_hour             end_hour              break_start           break_end             next_start_time
1970-01-01 08:00:00   1970-01-01 18:00:00   1970-01-01 09:30:00   1970-01-01 09:45:00   1970-01-01 11:00:00
1970-01-01 08:00:00   1970-01-01 18:00:00   1970-01-01 11:00:00   1970-01-01 11:30:00   1970-01-01 15:00:00
1970-01-01 08:00:00   1970-01-01 18:00:00   1970-01-01 15:00:00   1970-01-01 15:15:00   (NULL)

UNION часть:

Эта часть состоит из трех частей. Потому что мне нужно объединить временные ряды из обеих таблиц:

1. Принимая час работы. Сгенерируйте временной ряд до начала первого перерыва.

Для этого мне нужна только первая строка из приведенного выше CTE. Вот почему используется LIMIT 1.

2. Для разрывов все: Создать временной ряд от конца текущего перерыва до начала следующего перерыва.

CTE содержит оконная функцияlead(), который сдвигает start_time следующей строки в текущую (посмотрите на последний столбец результата CTE). Так что теперь я могу получить любое время перерыва, независимо от его количества. В моем примере я добавил третий разрыв с 9:30 на 9:45, чтобы продемонстрировать это. Таким образом, следующий временной ряд может быть сгенерирован из всех этих столбцов (текущие от break_end до next_start_time). Только последняя строка не содержит next_start_time, потому что его нет.

3. Последний шаг: сгенерируйте временной ряд от последнего перерыва, заканчивающегося до часа закрытия.

Это очень похоже на (1). После повторения всех времен перерыва я должен добавить последний временной ряд от времени последнего перерыва ко времени закрытия. Этого можно добиться либо путем фильтрации строки без next_start_time, либо путем сортировки DESC и использования LIMIT 1, как это сделал я.


Более сложный случай с большим количеством типов дней:

демонстрация: db <> рабочий пример

WITH hours AS (
    SELECT 
        oh.id as day_id,
        oh.open_hour + '1970-01-01'::date as open_hour, 
        oh.end_hour + '1970-01-01'::date as end_hour, 
        bh.start_time + '1970-01-01'::date as break_start,
        bh.end_time + '1970-01-01'::date as break_end,
        lead(start_time + '1970-01-01'::date) OVER (PARTITION BY oh.id ORDER BY start_time) as next_start_time
    FROM open_hours oh
    LEFT JOIN break_hours bh
    ON oh.id = bh.start_date
)

SELECT day_id, generate_series(open_hour, break_start, interval '30 minutes')::time as time_slot 
FROM (
    SELECT DISTINCT ON (day_id)
        day_id, open_hour, break_start
    FROM hours
    ORDER BY day_id, break_start
)s

UNION 

SELECT 
    day_id, generate_series(break_end, next_start_time, interval '30 minutes')::time
FROM ( 
    SELECT  
        day_id, break_end, next_start_time
    FROM
        hours
    WHERE next_start_time IS NOT NULL
) s

UNION

SELECT day_id, generate_series(break_end, end_hour, interval '30 minutes')::time 
FROM (
    SELECT DISTINCT ON (day_id)
        day_id, break_end, end_hour
    FROM hours
    ORDER BY day_id, break_start DESC
) s

ORDER BY day_id, time_slot

Основная идея остается такой же, как в примере, всего на один день. Разница в том, что мы должны учитывать разные типы дней. Я расширил приведенный выше пример и добавил второй день с разными часами работы и перерывами.

Изменения:

  1. Оконная функция в CTE получила часть PARTITION BY. Это гарантирует, что смещаются только те start_time, которые содержат в тот же день.
  2. LIMIT 1 больше не будет работать, потому что он ограничивает всю таблицу одной строкой. Это было изменено на DISTINCT ON (day_id), который ограничивает таблицу первой строкой каждого дня.

Спасибо за ответ. Но мне нужно общее решение, если у меня несколько перерывов, мне нужно добавить больше профсоюзов, верно?

Shamseer Pc 26.10.2018 09:26

Изменил ответ, расширил до многих дней, добавил пояснения

S-Man 26.10.2018 10:17

Спасибо за правильный ответ. Я искал такое же решение.

Shamseer Pc 27.10.2018 15:40

Другие вопросы по теме