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


create table mytable(id int, datum date, otherinfo varchar(100));
insert into mytable (id, datum, otherinfo)
values
(1, date '2019-12-28', 'testdata1'),
(2, date '2019-12-28', 'testdata2'),
(3, date '2019-12-29', 'testdata3'),
(4, date '2019-12-29', 'testdata4'),
(5, date '2019-12-31', 'testdata5-begin longest consecutive days'),
(6, date '2019-12-31', 'testdata6'),
(7, date '2020-01-01', 'testdata7'),
(8, date '2020-01-01', 'testdata8'),
(9, date '2020-01-02', 'testdata9'),
(10, date '2020-01-03', 'testdata10'),
(11, date '2020-01-04', 'testdata11'),
(12, date '2020-01-04', 'testdata12'),
(13, date '2020-01-05', 'testdata13-end longest consecutive days'),
(14, date '2020-01-22', 'testdata14'),
(15, date '2020-01-29', 'testdata15'),
(16, date '2020-01-30', 'testdata16');
16 rows affected

select count(*), min(datum), max(datum)
from
(
  select distinct 
    datum,
    datum - date '1900-01-01' - dense_rank() over (order by datum) as grp
  from mytable
) grouped
group by grp
order by grp;


count
min
max


2
2019-12-28
2019-12-29

6
2019-12-31
2020-01-05

1
2020-01-22
2020-01-22

2
2020-01-29
2020-01-30


with distinct_dates as
  (select distinct datum from mytable)
, grouped as
  (select
    datum,
    datum - date '1900-01-01' - row_number() over (order by datum) as grp
   from distinct_dates
  )
select count(*), min(datum), max(datum)
from grouped
group by grp
order by grp;


count
min
max


2
2019-12-28
2019-12-29

6
2019-12-31
2020-01-05

1
2020-01-22
2020-01-22

2
2020-01-29
2020-01-30

count	min	max
2	2019-12-28	2019-12-29
6	2019-12-31	2020-01-05
1	2020-01-22	2020-01-22
2	2020-01-29	2020-01-30