Menu
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?
707
To fill missing data in PostgreSQL, we need to create a 'helper' date series table that contains all dates between minimum and maximum dates in our table, including missing date values as rows. We will join this helper table with our sales table. We will use generate_series in PostgreSQL to generate date series table.
What is PostgreSQL Cross Join? The PostgreSQL Cross Join is used to combine all possibilities of the multiple tables and returns the output, which contain each row from all the selected tables. The CROSS JOIN, further known as CARTESIAN JOIN that allows us to produce the Cartesian product of all related tables.
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.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
