Re: Split daterange into sub periods

Hellmuth Vargas <hivs77@xxxxxxxxx> · Thu, 5 Jul 2018 10:30:45 -0500

Hi

select ($$[$$|| to_char(min(n.dato),'YYYY-MM-DD') || $$,$$ || to_char(max(n.dato),'YYYY-MM-DD') || $$]$$)::daterange,    daterange(min(n.dato)::date,max(n.dato)::date)
from (
	select u.dato,anterior,(u.dato-anterior)::interval,sum(case when anterior is null or (u.dato -anterior)::interval='1 day'::interval  then 0 else 1 end) over(order by u.dato) as grupo
	from (
		select u.dato, lag(u.dato) over( order by u.dato) as anterior, lead(u.dato) over( order by u.dato)
		from (
			select * from generate_series(lower('[2018-01-01, 2018-01-31]'::daterange),upper('[2018-01-01, 2018-01-31]'::daterange),'1 day'::interval) as a(dato)
			except
			(
				select generate_series(lower(a.dato),upper(a.dato),'1 day'::interval) from (values('[2018-01-04,2018-01-06]'::daterange),('[2018-01-09,2018-01-12]'::daterange),('[2018-01-18,2018-01-19]'::daterange)) as a(dato)

			) 
		) as u order by u.dato
	) as u
) as n
group by grupo
order by 1

        daterange        |        daterange
-------------------------+-------------------------
 [2018-01-01,2018-01-04) | [2018-01-01,2018-01-03)
 [2018-01-08,2018-01-09) | empty
 [2018-01-14,2018-01-18) | [2018-01-14,2018-01-17)
 [2018-01-21,2018-02-02) | [2018-01-21,2018-02-01)
(4 rows)

El jue., 5 de jul. de 2018 a la(s) 10:39, Andreas Kretschmer (andreas@xxxxxxxxxxxxxxx) escribió:

    On 05.07.2018 15:49, hmidi slim wrote:

          Hi, 

          I'm looking for splitting a daterange into many subperiods
          following this example:

        Base Date: [2018-01-01, 2018-01-31]
        overlapped_periods: 

        1- [ 2018-01-04, 2018-01-06]
        2- [ 2018-01-09, 2018-01-12]
        3- [ 2018-01-18, 2018-01-19]

        I try to get such a result:

          1- [ 2018-01-01, 2018-01-03]
        2- [ 2018-01-07, 2018-01-08]
        3- [ 2018-01-13, 2018-01-17]
        4- [ 2018-01-20, 2018-01-31]

        The operator '-' does not support this :

              SELECT daterange('2018-01-01', '2018-01-31', '[]') - daterange('2018-01-04', '2018-01-06', '[]');

              I got this error:
ERROR: result of range difference would not be contiguous

Is there any operators to make the split of daterange?

    andreas@[local]:5432/test# \d hmidi

                    Table "public.hmidi"

     Column |   Type    | Collation | Nullable | Default 

    --------+-----------+-----------+----------+---------

     id     | integer   |           | not null | 

     d      | daterange |           |          | 

    Indexes:

        "hmidi_pkey" PRIMARY KEY, btree (id)

    andreas@[local]:5432/test# insert into hmidi values
    (1,'[2018-01-04,2018-01-06]');INSERT 0 1

    andreas@[local]:5432/test# insert into hmidi values
    (2,'[2018-01-09,2018-01-12]');INSERT 0 1

    andreas@[local]:5432/test# insert into hmidi values
    (3,'[2018-01-18,2018-01-19]');INSERT 0 1

    andreas@[local]:5432/test# with month as (select s::date from
    generate_series('2018-01-01'::date,
    '2018-01-31'::date,'1day'::interval) s), tmp as ( select month.s,
    case when hmidi.d @> month.s then 1 else NULL end as covered from
    month left join hmidi on month.s <@ hmidi.d),tmp2 as ( select *,
    coalesce((sum(case when covered = 1 then 1 else NULL end) over
    (order by s))+1,1) as p from tmp) select p, min(s), max(s) from tmp2
    where covered is null group by p order by p;

     p  |    min     |    max     

    ----+------------+------------

      1 | 2018-01-01 | 2018-01-03

      4 | 2018-01-07 | 2018-01-08

      8 | 2018-01-13 | 2018-01-17

     10 | 2018-01-20 | 2018-01-31

    (4 rows)

    Regards, Andreas

    -- 

    2ndQuadrant Deutschland

-- 
Cordialmente, 

Ing. Hellmuth I. Vargas S. 
Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate