To answer your question, I did the following (all of the code below can be found on the fiddle here):
I decided to use 2 approaches - a "classic" one and one using PostgreSQL's range types.
Approach 1 - starttime/endtime
CREATE TABLE test
(
t_id INTEGER NOT NULL,
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP NOT NULL,
costs INTEGER NOT NULL
);
- it's worth noting that it's best to get into the habit of using
TIMESTAMPTZ at all times - I've simply used TIMESTAMP here because it's that bit more legible and time zones aren't integral to the question.
Populate:
INSERT INTO test VALUES
(1, '2022-08-23 08:00', '2022-08-23 11:00', 150),
(2, '2022-08-23 08:30', '2022-08-23 09:30', 50);
First step:
SELECT
t_id,
GENERATE_SERIES(started_at, ended_at - INTERVAL '1 HOUR', '1 HOUR') AS st,
GENERATE_SERIES(started_at + INTERVAL '1 HOUR', ended_at, '1 HOUR') AS et,
costs
FROM test;
t_id st et costs
1 2022-08-23 08:00:00 2022-08-23 09:00:00 150
1 2022-08-23 09:00:00 2022-08-23 10:00:00 150
1 2022-08-23 10:00:00 2022-08-23 11:00:00 150
2 2022-08-23 08:30:00 2022-08-23 09:30:00 50
we use the incomparable PostgreSQL function GENERATE_SERIES (manual) - why the other vendors haven't implemented it yet is beyond me.
there's a bit of jiggery-pokery involved with adding and subtracting hours so that we don't go over the bounds of the started_at and ended_at timestamps.
2nd step:
I've left in extra fields so that you can easily see what's going on - remove as required.
WITH hours AS
(
SELECT
t_id,
GENERATE_SERIES(started_at, ended_at - INTERVAL '1 HOUR', '1 HOUR') AS st,
GENERATE_SERIES(started_at + INTERVAL '1 HOUR', ended_at, '1 HOUR') AS et,
costs
FROM test
)
SELECT
*,
COUNT(*) OVER (PARTITION BY t_id),
costs/COUNT(*) OVER (PARTITION BY t_id) AS hourly_cost
FROM hours;
Result:
t_id st et costs count hourly_cost
1 2022-08-23 08:00:00 2022-08-23 09:00:00 150 3 50
1 2022-08-23 09:00:00 2022-08-23 10:00:00 150 3 50
1 2022-08-23 10:00:00 2022-08-23 11:00:00 150 3 50
2 2022-08-23 08:30:00 2022-08-23 09:30:00 50 1 50
So, as per the data, we can see that each hour of each shift cost 50. I checked with a different cost (600) and got an hourly_cost of 200, so it appears to work generally.
Approach 2 - PostgreSQL range types
CREATE TABLE test2
(
t_id INTEGER NOT NULL,
st_et TSRANGE NOT NULL,
costs INTEGER NOT NULL
);
Populate:
INSERT INTO test2 VALUES
(1, '[2022-08-23 08:00, 2022-08-23 11:00)', 600), -- <<<=== Note 600!
(2, '[2022-08-23 08:30, 2022-08-23 09:30)', 50);
Note the [ and ) part of the timestamp ranges' INSERTs. These are INCLUSIVE and EXCLUSIVE bounds, so the first range extends from 2022-08-23 08:00 to the very last instant before 2022-08-23 11:00, but not 11:00 itself. This distinguishes it from the approach above where the shifts effectively overlap at the ranges' hourly boundaries.
This, plus an extensive array of Range/Multirange Functions and Operators (yes, there's also a multirange type!) can make for some very sophisticated analysis of ranges of dates/times/ints... the world is your oyster! We won't be using them here (well, hardly), but check out some of the examples/sites online - well worth any time spent!
As with many complex systems, it is sometimes difficult to do simple things - it is only when we delve more deeply that the usefulness and sophistication of the tools become apparent. So it is with multi-range types - the SQL in this case is a bit "hairy"...
The SQL:
SELECT
t_id,
(
'[' ||
GENERATE_SERIES(LOWER(st_et), UPPER(st_et) - INTERVAL '1 HOUR', '1 HOUR')::TEXT ||
', ' ||
GENERATE_SERIES(LOWER(st_et) + INTERVAL '1 HOUR', UPPER(st_et), '1 HOUR')::TEXT ||
')'
)::TSRANGE AS s_range, -- shift range
costs
FROM test2;
Result:
t_id s_range costs
1 ["2022-08-23 08:00:00","2022-08-23 09:00:00") 600
1 ["2022-08-23 09:00:00","2022-08-23 10:00:00") 600
1 ["2022-08-23 10:00:00","2022-08-23 11:00:00") 600
2 ["2022-08-23 08:30:00","2022-08-23 09:30:00") 50
and then it's simply a repeat of the approach taken in Approach 1 - see fiddle.
