Django + PostgreSQL: Fill missing dates in a range

I do not think you can do this with pure Django ORM, and I am not even sure if this can be done neatly with extra(). The Django ORM is incredibly good in handling the usual stuff, but for more complex SQL statements and requirements, more so with DBMS specific implementations, it is just not quite there yet. You might have to go lower and down to executing raw SQL directly, or offload that requirement to be done by the application layer.

You can always generate the missing dates using Python, but that will be incredibly slow if the range and number of elements are huge. If this is being requested by AJAX for other use (e.g. charting), then you can offload that to Javascript.

In stead of the recursive CTE you could use generate_series() to construct a calendar-table:

SELECT calendar, count(mt.zdate) as THE_COUNTFROM generate_series('2015-07-20'::date                   , '2015-07-24'::date                   , '1 day'::interval)  calendarLEFT JOIN my_table mt ON mt.zdate = calendarGROUP BY 1ORDER BY 1 ASC;

BTW: I renamed date to zdate. DATE is a bad name for a column (it is the name for a data type)