Question: I have the following table: id | number_of _trip | start_date | end_date | seconds 1 637hui 2022-03-10 01:20:00 2022-03-10 01:32:00 720 2 384nfj 2022-03-10 02:18:00 2022-03-10 02:42:00 1440 3 102fiu 2022-03-10 02:10:00 2022-03-10 02:23:00 780 4 948pvc 2022-03-10 02:40:00 2022-03-10 03:20:00 2400 5 473mds 2022-03-10 02:45:00 2022-03-10 02:58:00 780 6 103fkd 2022-03-10 03:05:00 2022-03-10 03:28:00 1380 7 905783 2022-03-10 03:12:00 null 0 8 498wsq 2022-03-10 05:30:00 2022-03-10 05:48:00 1080  I want to get the time that is driven for each hour, but if a trip takes the space of two hours, the time must be taken for each hour. If the end of the trip has not yet finished, the end_date field is null, but it must count the time it is taking in the respective hours from start_date. I have the following query: SELECT time_bucket(bucket_width := INTERVAL '1 hour',ts := start_date, "offset" := '0 minutes') AS init_date, sum(seconds) as seconds FROM trips WHERE start_date >= '2022-03-10 01:00:00' AND start_date <= '2022-03-10 06:00:00' GROUP BY init_date ORDER BY init_date;  The result is: | init_date | seconds 2022-03-10 01:00:00 720 2022-03-10 02:00:00 5400 2022-03-10 03:00:00 1380 2022-03-10 05:00:00 1080  However I expect to receive a result like this: | init_date | seconds solo como una ayuda visual 2022-03-10 01:00:00 720 id(1:720) 2022-03-10 02:00:00 4200 id(2: 1440 3: 780 4: 1200 5: 780) 2022-03-10 03:00:00 5460 id(4:1200 6:1380 7:2880) 2022-03-10 05:00:00 1080 id(8:1080)  EDIT If I replace the null the result is still unwanted: | init_date | seconds 2022-03-10 01:00:00 720 2022-03-10 02:00:00 5400 2022-03-10 03:00:00 1380 2022-03-10 05:00:00 1080  I have been thinking about getting all the data and solving the problem with pandas. I'll try and post if I get the answer. EDIT My previous result was not entirely correct, since there were hours left of a trip that has not yet finished, the correct result should be:  start_date seconds 0 2022-03-10 01:00:00 720 1 2022-03-10 02:00:00 4200 2 2022-03-10 03:00:00 5460 3 2022-03-10 04:00:00 3600 4 2022-03-10 05:00:00 4680  NEW CODE def bucket_count(bucket, data): result = pd.DataFrame() list_r = [] for row_bucket in bucket.to_dict('records'): inicio = row_bucket['start_date'] fin = row_bucket['end_date'] df = data[ (inicio <= data['end_date']) & (inicio <= fin) & (data['start_date'] <= fin) & (data['start_date'] <= data['end_date']) ] df_dict = df.to_dict('records') for row in df_dict: seconds = 0 if row['start_date'] >= inicio and fin >= row['end_date']: seconds = (row['end_date'] - row['start_date']).total_seconds() elif row['start_date'] <= inicio <= row['end_date'] <= fin: seconds = (row['end_date'] - inicio).total_seconds() elif inicio <= row['start_date'] <= fin <= row['end_date']: seconds = (fin - row['start_date']).total_seconds() elif row['start_date'] < inicio and fin < row['end_date']: seconds = (fin - inicio).total_seconds() row['start_date'] = inicio row['end_date'] = fin row['seconds'] = seconds list_r.append(row) result = pd.DataFrame(list_r) return result.groupby(['start_date'])["seconds"].apply(lambda x: x.astype(int).sum()).reset_index() 

Answer: This can be done in plain sql (apart from time_bucket function), in a nested sql query: select interval_start, sum(seconds_before_trip_ended - seconds_before_trip_started) as seconds from ( select interval_start, greatest(0, extract(epoch from start_date - interval_start)::int) as seconds_before_trip_started, least(3600, extract(epoch from coalesce(end_date, '2022-03-10 06:00:00') - interval_start)::int) as seconds_before_trip_ended from ( select generate_series( (select min(time_bucket(bucket_width := INTERVAL '1 hour', ts := start_date, "offset" := '0 minutes')) from trips), (select max(time_bucket(bucket_width := INTERVAL '1 hour', ts := coalesce(end_date, '2022-03-10 06:00:00'), "offset" := '0 minutes')) from trips), '1 hour') as interval_start) i join trips t on t.start_date <= i.interval_start + interval '1 hour' and coalesce(t.end_date, '2022-03-10 06:00:00') >= interval_start ) subq group by interval_start order by interval_start;  This gives me the following result:  interval_start | seconds ---------------------+--------- 2022-03-10 01:00:00 | 720 2022-03-10 02:00:00 | 4200 2022-03-10 03:00:00 | 5460 2022-03-10 04:00:00 | 3600 2022-03-10 05:00:00 | 4680 2022-03-10 06:00:00 | 0 (6 rows)  Explanation Let's break the query down. In the innermost query: select generate_series( (select min(time_bucket(bucket_width := INTERVAL '1 hour', ts := start_date, "offset" := '0 minutes')) from trips), (select max(time_bucket(bucket_width := INTERVAL '1 hour', ts := coalesce(end_date, '2022-03-10 06:00:00'), "offset" := '0 minutes')) from trips), '1 hour' ) as interval_start  we generate a series of time interval starts - from minimal start_date value up to the maximal end_time value, truncated to full hours, with 1-hour step. Each boundary can obviously be replaced with an arbitrary datetime. Direct result of this query is the following:  interval_start --------------------- 2022-03-10 01:00:00 2022-03-10 02:00:00 2022-03-10 03:00:00 2022-03-10 04:00:00 2022-03-10 05:00:00 2022-03-10 06:00:00 (6 rows)  Then, the middle-level query joins this series with the trips table, joining rows if and only if any part of the trip took place during the hour-long interval beginning at the time given by the 'interval_start' column: select interval_start, greatest(0, extract(epoch from start_date - interval_start)::int) as seconds_before_trip_started, least(3600, extract(epoch from coalesce(end_date, '2022-03-10 06:00:00') - interval_start)::int) as seconds_before_trip_ended from ( -- innermost query select generate_series( (select min(time_bucket(bucket_width := INTERVAL '1 hour', ts := start_date, "offset" := '0 minutes')) from trips), (select max(time_bucket(bucket_width := INTERVAL '1 hour', ts := coalesce(end_date, '2022-03-10 06:00:00'), "offset" := '0 minutes')) from trips), '1 hour' ) as interval_start -- innermost query end ) intervals join trips t on t.start_date <= intervals.interval_start + interval '1 hour' and coalesce(t.end_date, '2022-03-10 06:00:00') >= intervals.interval_start  The two computed values represent respectively:  seconds_before_trip_started - number of second passed between the beginning of the interval, and the beginning of the trip (or 0 if the trip begun prior to interval start). This is the time the trip didn't take place - thus we will be substructing it in the following step seconds_before_trip_ended - number of seconds passed between the end of the interval, and the end of the trip (or 3600 if the trip didn't end within concerned interval).  The outermost query substracts the two beformentioned fields, effectively computing the time each trip took in each interval, and sums it for all trips, grouping by interval: select interval_start, sum(seconds_before_trip_ended - seconds_before_trip_started) as seconds from ( -- middle-level query select interval_start, greatest(0, extract(epoch from start_date - interval_start)::int) as seconds_before_trip_started, least(3600, extract(epoch from coalesce(end_date, '2022-03-10 06:00:00') - interval_start)::int) as seconds_before_trip_ended from ( select generate_series( (select min(time_bucket(bucket_width := INTERVAL '1 hour', ts := start_date, "offset" := '0 minutes')) from trips), (select max(time_bucket(bucket_width := INTERVAL '1 hour', ts := coalesce(end_date, '2022-03-10 06:00:00'), "offset" := '0 minutes')) from trips), '1 hour') as interval_start) i join trips t on t.start_date <= i.interval_start + interval '1 hour' and coalesce(t.end_date, '2022-03-10 06:00:00') >= interval_start -- middle-level query end ) subq group by interval_start order by interval_start;  Additional grouping In case we have another column in the table, and what we really need is the segmentation of the above result in respect to that column, we simply need to add it to the appropriate select and group by clauses (optionally to order by clause as well). Suppose there's an additional driver_id column in the trips table:  id | number_of_trip | start_date | end_date | seconds | driver_id ----+----------------+---------------------+---------------------+---------+----------- 1 | 637hui | 2022-03-10 01:20:00 | 2022-03-10 01:32:00 | 720 | 0 2 | 384nfj | 2022-03-10 02:18:00 | 2022-03-10 02:42:00 | 1440 | 0 3 | 102fiu | 2022-03-10 02:10:00 | 2022-03-10 02:23:00 | 780 | 1 4 | 948pvc | 2022-03-10 02:40:00 | 2022-03-10 03:20:00 | 2400 | 1 5 | 473mds | 2022-03-10 02:45:00 | 2022-03-10 02:58:00 | 780 | 1 6 | 103fkd | 2022-03-10 03:05:00 | 2022-03-10 03:28:00 | 1380 | 2 7 | 905783 | 2022-03-10 03:12:00 | | 0 | 2 8 | 498wsq | 2022-03-10 05:30:00 | 2022-03-10 05:48:00 | 1080 | 2  The modified query would look like that: select interval_start, driver_id, sum(seconds_before_trip_ended - seconds_before_trip_started) as seconds from ( select interval_start, driver_id, greatest(0, extract(epoch from start_date - interval_start)::int) as seconds_before_trip_started, least(3600, extract(epoch from coalesce(end_date, '2022-03-10 06:00:00') - interval_start)::int) as seconds_before_trip_ended from ( select generate_series( (select min(time_bucket(bucket_width := INTERVAL '1 hour', ts := start_date, "offset" := '0 minutes')) from trips), (select max(time_bucket(bucket_width := INTERVAL '1 hour', ts := coalesce(end_date, '2022-03-10 06:00:00'), "offset" := '0 minutes')) from trips), '1 hour') as interval_start ) intervals join trips t on t.start_date <= intervals.interval_start + interval '1 hour' and coalesce(t.end_date, '2022-03-10 06:00:00') >= intervals.interval_start ) subq group by interval_start, driver_id order by interval_start, driver_id;  and give the following result:  interval_start | driver_id | seconds ---------------------+-----------+--------- 2022-03-10 01:00:00 | 0 | 720 2022-03-10 02:00:00 | 0 | 1440 2022-03-10 02:00:00 | 1 | 2760 2022-03-10 03:00:00 | 1 | 1200 2022-03-10 03:00:00 | 2 | 4260 2022-03-10 04:00:00 | 2 | 3600 2022-03-10 05:00:00 | 2 | 4680 2022-03-10 06:00:00 | 2 | 0