skip to Main Content

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

2

Answers


  1. 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. 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