Блог

Я хотел бы сгенерировать массив всех временных диапазонов, которые находятся в пределах 4 часов вечера -9 часов вечера между начальной и конечной датой

 start_date          | end_date
----------------------------------------
2020-11-01 16:30:00 | 2020-11-03 18:30:00
 

Запрос должен быть способен превратить приведенную выше таблицу в:

 row | start_date          | end_date
------------------------------------------------
1   | 2020-11-01 16:30:00 | 2020-11-01 21:00:00
------------------------------------------------
2   | 2020-11-02 16:00:00 | 2020-11-02 21:00:00
------------------------------------------------
3   | 2020-11-03 16:00:00 | 2020-11-03 18:30:00
 

Может ли кто-нибудь указать мне правильное направление в том, как подойти к этому?

Я бы сделал это так:

 with input as (
  select '2020-11-01 16:30:00'::timestamptz as start_date,
         '2020-11-03 18:30:00'::timestamptz as end_date
)
select row_number() over (order by ddate) as row,
       case 
         when     start_date::date = ddate  
              and start_date > ddate   interval '16 hours' 
           then start_date
         else ddate   interval '16 hours'
       end as start_date,
       case
         when     end_date::date = ddate
              and end_date < ddate   interval '21 hours'
           then end_date
         else ddate   interval '21 hours'
       end as end_date 
  from input
       cross join lateral 
         generate_series(
           case 
             when start_date::time > '21:00' then start_date::date   interval '1 day'
             else start_date::date
           end, 
           case 
             when end_date::time < '16:00' then end_date::date - interval '1 day'
             else end_date::date
           end, 
           interval '1 day') as gs(ddate)
;
┌─────┬────────────────────────┬────────────────────────┐
│ row │       start_date       │        end_date        │
├─────┼────────────────────────┼────────────────────────┤
│   1 │ 2020-11-01 16:30:00-05 │ 2020-11-01 21:00:00-05 │
│   2 │ 2020-11-02 16:00:00-05 │ 2020-11-02 21:00:00-05 │
│   3 │ 2020-11-03 16:00:00-05 │ 2020-11-03 18:30:00-05 │
└─────┴────────────────────────┴────────────────────────┘
(3 rows)

 

Когда вы используете generate_series с часами вместо дней, как в решении @MikeOrganek, вы можете фильтровать их напрямую. Начальные и конечные часы должны быть «полными часами», что может быть достигнуто с помощью date_trunc() . Поскольку date_trunc сокращаются последние минуты конечной даты, имеет смысл создать ряд на час больше; вот почему в примере добавлен час.

Наконец GROUP BY , укажите дату и укажите MIN MAX время. С least() помощью и greatest() вы можете настроить граничные случаи.

пошаговая демонстрация: db<>fiddle

 SELECT
    GREATEST(MIN(gs), start_date) as start_time,
    LEAST(MAX(gs), end_date) as end_time
FROM
    t,
    generate_series(
        date_trunc('hour', start_date), 
        date_trunc('hour', end_date)   interval '1 hour',
        interval '1 hour'
    ) as gs
WHERE gs::time >= '16:00:00' and gs::time <= '21:00:00'
GROUP BY start_date, end_date, gs::date
ORDER BY gs::date
 

Другим решением является использование generate_series() с интервалом в один день и построение временной части в зависимости от значения времени в начальной / конечной дате для первого и последнего дня.

 with from_to (start_date, end_date) as (
 values (timestamp '2020-11-01 16:30:00', timestamp '2020-11-03 18:30:00')
)
select g.nr as row, 
       g.d::date   case 
                      when g.d::date = start_date::date and start_date::time > time '16:00' then start_date::time
                      else time '16:00'
                   end as start_date,
       g.d::date   case 
                     when g.d::date = end_date::date and end_date::time < time '21:00' then end_date::time
                     else time '21:00'
                  end as end_date
from from_to 
  cross join generate_series(start_date, end_date, interval '1 day') with ordinality as g(d,nr)
order by g.nr;  
 

Онлайн-пример