How to split start and end time columns into discrete pieces using PostgreSQL?

Question

We have several tables that have a structure such as:

start, - datetime
end, - datetime
cost - decimal

So, for example, there could be a string like:

01/01/2010 10:08, 01/01/2010 13:56, 135.00
01/01/2010 11:01, 01/01/2010 15:22, 118.00
01/01/2010 06:19, 01/02/2010 1:43, 167.00

Etc ...

I would like to get this in a format (with a function?) That returns data in a format like:

10:00, 10:15, X, Y, Z
10:15, 10:30, X, Y, Z
10:30, 10:45, X, Y, Z
10:45, 11:00, X, Y, Z
11:00, 11:15, X, Y, Z

....

Where:
X = number of rows that match Y = cost / expense for this piece of time
Z = total amount of time during this duration

IE, for the above data, we could have:

10:00, 10:15, 1, (135/228 minutes * 7), 7

• The first line starts at 10:08, so only 7 minutes are used from 10: 00-10: 15.
• In the beginning → the end time is 228 minutes.

....

11:00, 11:15, 2, ((135 + 118) / ((228 + 261) minutes * (15 + 14)), 29

• The second line starts immediately after 11:00, so we need 15 minutes from the first line, plus 14 minutes from the second line.
• In the second start → the end time is 261 minutes.

....

I believe I did the math right here, but I need to figure out how to do this in the PG function so that it can be used in the report.

Ideally, I would like to be able to call a function with some arbitrary duration, i.e. 15minute, or 30minute, or 60minute, and split it based on this.

Any ideas?

Answer 1

Here is my attempt. Given this table definition:

CREATE TABLE interval_test ( "start" timestamp without time zone, "end" timestamp without time zone, "cost" integer ) 

This query seems to do what you want. Not sure if this is the best solution. Also note that Postgres 8.4 needs to work because it uses the WINDOW and WITH functions with queries.

 WITH RECURSIVE intervals(period_start) AS ( SELECT date_trunc('hour', MIN(start)) AS period_start FROM interval_test UNION ALL SELECT intervals.period_start + INTERVAL '15 MINUTES' FROM intervals WHERE (intervals.period_start + INTERVAL '15 MINUTES') < (SELECT MAX("end") FROM interval_test) ) SELECT DISTINCT period_start, intervals.period_start + INTERVAL '15 MINUTES' AS period_end, COUNT(*) OVER (PARTITION BY period_start ) AS record_count, SUM (LEAST(period_start + INTERVAL '15 MINUTES', "end")::timestamp - GREATEST(period_start, "start")::timestamp) OVER (PARTITION BY period_start ) AS total_time, (SUM(cost) OVER (PARTITION BY period_start ) / (EXTRACT(EPOCH FROM SUM("end" - "start") OVER (PARTITION BY period_start )) / 60)) * ((EXTRACT (EPOCH FROM SUM (LEAST(period_start + INTERVAL '15 MINUTES', "end")::timestamp - GREATEST(period_start, "start")::timestamp) OVER (PARTITION BY period_start )))/60) AS expense FROM interval_test INNER JOIN intervals ON (intervals.period_start, intervals.period_start + INTERVAL '15 MINUTES') OVERLAPS (interval_test.start, interval_test.end) ORDER BY period_start ASC