Finding Missing Dates (PostgreSQL)

Question

I have one table containing a d_date column. In this column, specify only the date value.

My question is: "I want to find missing dates from this column (d_date)."

Example: This column contains dates from "January 1 to 2007" to "January 7, 2007" then I want to find the missing date between "January 1, 2007" to "January 10, 2007, and my result should be" 8-Jan-2007 "," 9-Jan-2007 "," 10-Jan-2007 ".

So how can I get this result?

Answer 1

You can compare the date series created using the generate_series(start, stop, step interval) function with the dates in the table:

 SELECT * FROM generate_series('2007-01-01', '2007-01-10', interval '1 day') AS dates WHERE dates NOT IN (SELECT d_date FROM your_table); 

Additional information on the generate_series function from the PostgreSQL documentation: 9.24. Set return functions .

Answer 2

You can use WITH RECURSIVE to create a date table, and then select dates that are not listed in your table:

 WITH RECURSIVE t(d) AS ( (SELECT '2015-01-01'::date) UNION ALL (SELECT d + 1 FROM t WHERE d + 1 <= '2015-01-10') ) SELECT d FROM t WHERE d NOT IN (SELECT d_date FROM tbl); 

Edit: Note that a recursive CTE may be redundant. Epikuros answer provides a simpler solution with generate_series .