Postgres разделение строк на минуты интервалов

Вопрос или проблема

CREATE TABLE t (
    "start_datetime" TIMESTAMP,
    "end_datetime" TIMESTAMP,
    "duration" INT,
    "id" INTEGER
);

INSERT INTO t
("start_datetime", "end_datetime", "duration", "id")
VALUES
('2021-10-17 03:13:00', '2021-10-17 03:15:02', '302', '6214550'),
('2021-10-17 03:15:02', '2021-10-17 04:17:03', '4021', '6214551');

SELECT id
     -- Поскольку мы округлили время до минуты в generate_series, нам нужно обработать первую дату иначе,
     -- выбирая её вместо любых временных меток, предшествующих ей
     , CASE WHEN start_datetime > m THEN start_datetime ELSE m END AS start_datetime
     -- Мы находим следующую минуту (то есть end_datetime) с помощью LEAD(), кроме последней строки (по id), так как её не существует.
     -- Когда это так, мы используем end_datetime
     , COALESCE(LEAD(m) OVER(PARTITION BY id ORDER BY m), t.end_datetime) AS end_datetime
FROM t, generate_series(date_trunc('minute', start_datetime), end_datetime, '1 minute'::interval) AS f(m)
ORDER BY id, m;

select greatest(slot,start_datetime)   as start_datetime
     , least(slot+'1min',end_datetime) as end_datetime
     , id
from test
cross join lateral generate_series( date_trunc('minute',start_datetime)
                                   ,end_datetime
                                   ,'1min') as slot;

Ответ или решение

Чтобы разделить строки таблицы PostgreSQL на интервалы в одну минуту на основе столбцов start_datetime и end_datetime, можно использовать функцию generate_series в комбинации с CROSS JOIN. Это позволит создать новые строки для каждого временного интервала в минуту.

Вот полное решение:

Шаг 1: Создание таблицы и вставка данных

Сначала создаем таблицу и заполняем её примерами данных:

CREATE TABLE t (
    start_datetime TIMESTAMP,
    end_datetime TIMESTAMP,
    duration INT,
    id INTEGER
);

INSERT INTO t
(start_datetime, end_datetime, duration, id)
VALUES
('2021-10-17 03:13:00', '2021-10-17 03:15:02', 302, 6214550),
('2021-10-17 03:15:02', '2021-10-17 04:17:03', 4021, 6214551);

Шаг 2: Создание запроса для разделения на интервалы

Теперь, используя generate_series, мы можем разделить временные промежутки на интервалы в одну минуту. Ключевым моментом будет использование функций GREATEST и LEAST, чтобы корректно обработать начало и конец каждого интервала.

SELECT 
    GREATEST(slot, start_datetime) AS start_datetime,
    LEAST(slot + interval '1 minute', end_datetime) AS end_datetime,
    id
FROM 
    t
CROSS JOIN LATERAL 
    generate_series(date_trunc('minute', start_datetime), end_datetime, '1 minute') AS slot
ORDER BY id, start_datetime;

Пояснение

1. CROSS JOIN LATERAL: Используется для того, чтобы сгенерировать серию минут, начиная с округлённого вниз значения start_datetime и заканчив на end_datetime.

2. GREATEST и LEAST:

   • GREATEST(slot, start_datetime): берёт максимальное значение между сгенерированным интервалом и фактическим временем начала.
   • LEAST(slot + interval '1 minute', end_datetime): выбирает меньшее значение из конца интервала в одну минуту и фактического end_datetime, что позволяет ограничивать временные промежутки по максимально допустимому времени.

Результат

Запрос вернёт несколько строк для каждой уникальной записи в таблице, которые будут в указанных временных интервалах по минутам. Например, для приведённых данных результат будет следующим:

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