Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


begin;
create table order_history(id serial,pickup_date date,   return_date date);
insert into order_history(pickup_date,return_date) values('2020-03-01', '2020-03-12'),
('2020-03-01', '2020-03-22'),
('2020-03-11', '2020-03-22'),
('2020-02-11', '2020-03-22'),
('2020-01-01', '2020-01-22'),
('2020-01-01', '2020-04-22');
commit;
6 rows affected

alter table order_history add column date_ranges daterange;
with a(m_begin,  m_end, pickup_date) as
(select date_trunc('month', pickup_date)::date,
 (date_trunc('month', pickup_date) + interval '1 month - 1 day')::date,
        pickup_date from order_history)
update order_history set date_ranges =
    daterange(a.m_begin, a.m_end,'[]') from a
        where a.pickup_date = order_history.pickup_date;
6 rows affected

select *, return_date - pickup_date as total from order_history order by id;


id
pickup_date
return_date
date_ranges
total


1
2020-03-01
2020-03-12
[2020-03-01,2020-04-01)
11

2
2020-03-01
2020-03-22
[2020-03-01,2020-04-01)
21

3
2020-03-11
2020-03-22
[2020-03-01,2020-04-01)
11

4
2020-02-11
2020-03-22
[2020-02-01,2020-03-01)
40

5
2020-01-01
2020-01-22
[2020-01-01,2020-02-01)
21

6
2020-01-01
2020-04-22
[2020-01-01,2020-02-01)
112


--if pickup_date and return_date in the same month then return_date - pickup_date
--else aggreagte based pickup_date.
--for example. id=4, total is 40. 40=22+18. 
--therefore  should split to march-2020 22 feb-2020:18.
--finally do the aggregate per month. 
--id=6. total = 112. 112 = 22+ 31 + 29 + 30
--therefore toal should split: jan2020: 30, feb2020:29, march2020: 31, 2020apr:22. 

id	pickup_date	return_date	date_ranges	total
1	2020-03-01	2020-03-12	[2020-03-01,2020-04-01)	11
2	2020-03-01	2020-03-22	[2020-03-01,2020-04-01)	21
3	2020-03-11	2020-03-22	[2020-03-01,2020-04-01)	11
4	2020-02-11	2020-03-22	[2020-02-01,2020-03-01)	40
5	2020-01-01	2020-01-22	[2020-01-01,2020-02-01)	21
6	2020-01-01	2020-04-22	[2020-01-01,2020-02-01)	112