Find missing dates (PostgreSQL)

postgresql

Solution

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.

Problem

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?

Original source