CODEWITHSUNDEEP
postgresql
Shiva
Shiva

Reputation: 12514

Postgres - Splitting a row into multiple rows based on two datetime fields to get logged minutes each hour

What I want to Achieve.

I have a table called calls which has columns started_at:datetime, end_at:datetime, handler:string.

id started_at end_at handler agent_id
1 2021-04-05T02:00:00Z 2021-04-05T03:30:00Z fg456fghj 1
2 2021-04-05T03:40:00Z 2021-04-05T04:30:00Z cvbnmfghh 1
3 2021-04-05T04:40:00Z 2021-04-05T05:00:00Z wertyuuuu 1
4 2021-04-06T01:40:00Z 2021-04-05T02:00:00Z 34sdfertt 1

I want a new table that looks like this

id hour minutes_logged
1 02 of 2021-04-05 60
2 03 of 2021-04-05 50
3 04 of 2021-04-05 50
4 01 of 2021-04-06 20

Note: minutes_logged cannot exceed 60 minutes as an hour only has 60 minutes.

My Findings

For the hour column I could write is

concat(extract(hour from calls.started_at), ' of ', calls.started_at::date) as hour

But I could not write for minutes_logged properly.

Main problem

I could not write a query to break the time range into multiple rows. Please help. I appreciate your effort. Thanks in advance.

Upvotes: 1

Views: 337

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

We can approach this by first generating a calendar table covering all minutes on all dates in your data set. Then, inner join to your data table and aggregate by the hour (and day) to generate the counts:

WITH dates AS (
    SELECT '2021-04-05'::date AS dt UNION ALL
    SELECT '2021-04-06'::date
),
minutes AS (
    SELECT (n || ' minute')::INTERVAL AS min
    FROM generate_series(0, 1439) n
)

SELECT
    DATE_TRUNC('hour', d.dt + m.min),
    COUNT(*) AS minutes_logged
FROM dates d
CROSS JOIN minutes m
INNER JOIN yourTable t
   ON d.dt + m.min >= t.started_at AND d.dt + m.min < t.end_at
GROUP BY DATE_TRUNC('hour', d.dt + m.min)
ORDER BY DATE_TRUNC('hour', d.dt + m.min);

screen capture from demo link below

Demo

Note: To support wide range of start and end dates following can be implemented


    with dates AS (
        select * from generate_series(timestamp '2019-12-08'::date, '2021-04-09'::date, '1 day') as dt
    ),

Upvotes: 1

Related Questions