How to Find Consecutive Dates in Postgres SQL

Question

I have the following table in the postgres database (the table name is table_test):

          id             dia          Data_sensor_Analog
         2165         2020-09-20       4585542
         2165         2020-09-21       4954566
         2165         2020-09-26           255 

I would like to count how many consecutive days have the attribute dia.

For this, I tried to make the following code:

           WITH 

           groups AS (
           SELECT
              ROW_NUMBER() OVER (ORDER BY dia) AS rn,
              dateadd(dia, -ROW_NUMBER() OVER (ORDER BY dia), dia) AS grp,
              dia
           FROM table_test
          )

          SELECT
          COUNT(*) AS consecutiveDates,
          MIN(dia) AS minDate,
          MAX(dia) AS maxDate
          FROM groups
          GROUP BY grp
          ORDER BY 1 DESC, 2 DESC

I would like the output to be:

             consecutiveDates       minDate        maxDate  
                     1            2020-09-20      2020-09-21

However, when I run the code, the following error message appears:

          ERROR:  function dateadd(text, bigint, text) does not exist
          LINE 17:       dateadd(dia, -ROW_NUMBER() OVER (ORDER BY dia), dia) A

I'm using postgres and found this sample code on the website: https://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/

I transformed the dia attribute to:

         ALTER TABLE table_test
         ALTER COLUMN dia
         TYPE TIMESTAMP WITHOUT TIME ZONE
         USING dia::timestamp without time zone;

Answer 1

Considering you have only one entry for a day in your table then try this:

select id, count(*) -1 "count", max(dia), min(dia) from (
select *, 
date(dia) - row_number() over (partition by id order by date(dia)) * interval '1 day' "filter" 
from table_test
) t1 
group by id, filter
having count(*) -1 > 0

DEMO

In case you have multiple values for same date then try below:

with cte as (
select 
*,
date(dia) date_,date(dia) - dense_rank() over ( partition by id order by date(dia)) * interval '1 day' "filter" 
from table_test
)
select 
id, count(distinct date_) -1 "count" , max(dia),min(dia) 
from cte
group by id, filter
having count(distinct date_) -1 >0

DEMO

Answer 2

You can subtract an enumerated value, but you need a subquery or CTE:

select min(dia), max(dia), count(*)
from (select t.*,
             row_number() over (order by dia) as seqnum
      from table_test t
     ) t
group by dia - seqnum * interval '1 day';

However, it looks like dia is a string and not a date. To address that:

group by (dia::date) - seqnum * interval '1 day';

The format is fine for conversion to a date.

Here is a db<>fiddle.