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;

Postgresql functions are typed. If some argument in the call has the wrong type you will get this error. Find function definition and compare, then cast the argument to meet the function signature. Mainly it probably takes date time instead of text as input. — jlandercy
– jlandercy, Commented May 18, 2021 at 15:05

Akhilesh Mishra · Accepted Answer · 2021-05-18 16:51:37Z

5

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

edited May 18, 2021 at 16:51

answered May 18, 2021 at 16:02

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jane Borges Over a year ago

Worked perfectly! In the original table, with the same structure as the "table_test" table, I have other identifiers (id). The question is how would you count consecutive days by id?

Akhilesh Mishra Over a year ago

Its a minor change in dense_rank() or row_number() . updated the answer as per your requirement

Jane Borges Over a year ago

Worked perfectly!

Gordon Linoff · Accepted Answer · 2021-05-18 18:16:34Z

1

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.

edited May 18, 2021 at 18:16

answered May 18, 2021 at 15:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Jane Borges Over a year ago

I added a type transformation to the "dia" attribute in the question. Your code has executed. However, the answer doesn't just show the desired output. It is showing the "dia" 2020-09-26 as max and min and with count = 1. Do you know how I can solve it?

Gordon Linoff Over a year ago

@JaneBorges . . . Of course. If you just want rows with more than one date, add having count(*) > 1. Your question isn't really clear on what results you want.

Ion Ionets · Accepted Answer · 2022-11-03 11:47:02Z

0

PostrgeSQL doesn't support dateadd, here's the official docs: https://www.postgresql.org/docs/9.4/functions-datetime.html

So, for PG, the solution looks like:

       WITH 
dates(dia) AS (
    SELECT DISTINCT CAST(dia AS DATE)
     FROM table_test
  ),
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY dia) AS rn,
      (dia - make_interval(days :=  cast (ROW_NUMBER() OVER (ORDER BY dia will) AS INTEGER )  )) AS grp,
      date
    FROM dates
      )
 SELECT 
   COUNT(*) AS streak,
   MIN(date) AS startDate,
   MAX(date) AS endDate
 FROM groups
 GROUP BY grp
 ORDER BY 1 DESC, 2 DESC;

answered Nov 3, 2022 at 11:47

Ion Ionets

1131 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to Find Consecutive Dates in Postgres SQL

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related