PGSQL计算每月延期天数

该SQL可以实现一条记录变成多条记录

比如项目计划开始时间 1月1日，项目实际开始时间 4月1日，则会产生4条数据，1月延期31天，2月延期 28天，3 月延期31天

4月延期1天

select p.name as 项目名称,a.*,

case when a.计划开始月份=a.实际开始月份                       then date_part('day', cast(a.实际开始 as TIMESTAMP)- cast(a.计划开始 as TIMESTAMP))

     when a.实际开始>a.最后一天 and a.中间=a.计划开始         then   date_part('day', cast(a.最后一天 as TIMESTAMP)- cast(a.计划开始 as TIMESTAMP))

     when a.实际开始>a.最后一天 and a.中间<>a.计划开始 and a.中间月<>a.实际开始月份 then   date_part('day', cast(a.最后一天 as TIMESTAMP)- cast(a.中间月 as TIMESTAMP))+1

     when a.实际开始月份=a.中间月 and a.计划开始月份<>a.实际开始月份 then   date_part('day', cast(a.实际开始 as TIMESTAMP)- cast(a.实际开始月份 as TIMESTAMP))+1

else 0 end as 当月间隔

from (

select name

plan_start_time as 计划开始,to_char(plan_start_time,'YYYY-MM-01') AS 计划开始月份,

act_start_time  as 实际开始,to_char(act_start_time,'YYYY-MM-01') AS 实际开始月份,

generate_series(plan_start_time,act_start_time,'1 month' ) as 中间,

to_char(generate_series(plan_start_time,act_start_time,'1 month' ),'YYYY-MM-01') AS 中间月,

(date_trunc('month',generate_series(plan_start_time,act_start_time,'1 month' )) + interval '1 month - 1 day')::date as 最后一天

from 

project_list) as a

left join projects as p

on a.name=p.id::VARCHAR