11

I have one table that contain d_date column. In this column, contain date value only.

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

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

So, how can i get this result?

Akash Chavda
  • 1,185
  • 1
  • 11
  • 29

2 Answers2

21

You can compare a date series created with the function generate_series(start, stop, step interval) to dates in your 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);

More info on function generate_series from PostgreSQL documentation: 9.24. Set Returning Functions.

Simo Kivistö
  • 4,247
  • 3
  • 38
  • 42
0

You can use WITH RECURSIVE to build a table of dates and then select dates that are not 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: notice that a recursive CTE might be an overkill here. Epikuros's answer provides a simpler solution with generate_series.

Community
  • 1
  • 1
Ainar-G
  • 34,563
  • 13
  • 93
  • 119