Consecutive dates postgresql

Question

I need to know if there are no consecutive dates per document. I have this table:

document | the_day  
     1   | 2015-01-01  
     1   | 2015-01-02  
     1   | 2015-01-03  
     1   | 2015-01-04  
     1   | 2015-01-05  
     1   | 2015-01-06  
     2   | 2015-01-01  
     2   | 2015-01-03  
     2   | 2015-01-04  
     2   | 2015-01-05  
     2   | 2015-01-06  
     3   | 2015-01-01  
     3   | 2015-01-02  
     3   | 2015-01-03  
     3   | 2015-01-04  
     3   | 2015-01-05  
     3   | 2015-01-06

AS you can see there is only one gap: In document 2 the '2015-01-02' is missing. I want to know this gap. I have this select:

SELECT document, the_day, the_day - lag(the_day) OVER w AS gap
              FROM mytable
              where active=true and fault=false
               WINDOW w AS (ORDER BY document,the_day)

This select is giving me a register per date, and the gap, that in most of cases is 1, but when another document is starting in the result, it gives me the gap wrong. I dont know if this is the correct way or to make a function... Here the code to build the table:

--Table: public.test_consecutives

--DROP TABLE public.test_consecutives;

CREATE TABLE public.test_consecutives (
  document  integer,
  the_day   date
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.test_consecutives
  OWNER TO postgres;
INSERT INTO test_consecutives (document, the_day) VALUES
    (1, '2015-01-01'),
    (1, '2015-01-02'),
    (1, '2015-01-03'),
    (1, '2015-01-04'),
    (1, '2015-01-05'),
    (1, '2015-01-06'),
    (2, '2015-01-01'),
    (2, '2015-01-03'),
    (2, '2015-01-04'),
    (2, '2015-01-05'),
    (2, '2015-01-06'),
    (3, '2015-01-01'),
    (3, '2015-01-02'),
    (3, '2015-01-03'),
    (3, '2015-01-04'),
    (3, '2015-01-05'),
    (3, '2015-01-06');

Eggplant · Accepted Answer · 2015-05-05 08:22:58Z

3

If you don't specify a PARTITION PostgreSQL will assume it is the whole table. Your query should include the PARTITION BY clause:

SELECT document, the_day, the_day - lag(the_day) OVER w AS gap
    FROM mytable
    where active=true and fault=false
    WINDOW w AS (PARTITION BY document ORDER BY document,the_day)

answered May 5, 2015 at 8:22

Eggplant

2,0231 gold badge16 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Consecutive dates postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related