PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres split time ranges into 1 minute slots, without rounding boundaries

I have a postgres table with timestamp columns:

start_datetime end_datetime duration id
2021-10-17 03:13:00 2021-10-17 03:15:02 302 6214550
2021-10-17 03:15:02 2021-10-17 03:17:03 4,021 6214551

which i need to split out in to buckets constrained to either the end of the minute or the end_datetime as:

start_datetime end_datetime id
2021-10-17 03:13:00 2021-10-17 03:14:00 6214550
2021-10-17 03:14:00 2021-10-17 03:15:00 6214550
2021-10-17 03:15:00 2021-10-17 03:15:02 6214550
2021-10-17 03:15:02 2021-10-17 03:16:00 6214551
2021-10-17 03:16:00 2021-10-17 03:17:00 6214551
2021-10-17 03:17:00 2021-10-17 03:17:03 6214551
Tags:

2

Answers


  1. 0

    Schema (PostgreSQL v15)

    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');

    Query #1

    SELECT id
     -- Since we truncated down to the minute in the generate_series, we need to handle the first datetime differently,
     -- choosing it over any datetime before it
     , CASE WHEN start_datetime > m THEN start_datetime ELSE m END AS start_datetime
     -- We find the next minute (ie end_datetime) using LEAD(), except for the last row (per id) as it doesn't exist.
     -- When that's the case, we use 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;
    id start_datetime end_datetime
    6214550 2021-10-17T03:13:00.000Z 2021-10-17T03:14:00.000Z
    6214550 2021-10-17T03:14:00.000Z 2021-10-17T03:15:00.000Z
    6214550 2021-10-17T03:15:00.000Z 2021-10-17T03:15:02.000Z
    6214551 2021-10-17T03:15:02.000Z 2021-10-17T03:16:00.000Z
    6214551 2021-10-17T03:16:00.000Z 2021-10-17T03:17:00.000Z
    … truncated
    6214551 2021-10-17T04:16:00.000Z 2021-10-17T04:17:00.000Z
    6214551 2021-10-17T04:17:00.000Z 2021-10-17T04:17:03.000Z

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    Cross join each row to a generate_series() to spawn the 1-minute slots, use greatest() and least() to keep the non-aligned start and end timestamps.
    demo at db<>fiddle

    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;
    start_datetime end_datetime id
    2021-10-17 03:13:00 2021-10-17 03:14:00 6214550
    2021-10-17 03:14:00 2021-10-17 03:15:00 6214550
    2021-10-17 03:15:00 2021-10-17 03:15:02 6214550
    2021-10-17 03:15:02 2021-10-17 03:16:00 6214551
    2021-10-17 03:16:00 2021-10-17 03:17:00 6214551
    2021-10-17 03:17:00 2021-10-17 03:17:03 6214551
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search