Django + PostgreSQL: Fill missing dates in a range

Question

I have a table with one of the columns as date. It can have multiple entries for each date.

date         .....
-----------  -----
2015-07-20     ..
2015-07-20     ..
2015-07-23     ..
2015-07-24     ..

I would like to get data in the following form using Django ORM with PostgreSQL as database backend:

date         count(date)
-----------  -----------
2015-07-20        2
2015-07-21        0       (missing after aggregation)
2015-07-22        0       (missing after aggregation)
2015-07-23        1
2015-07-24        1

Corresponding PostgreSQL Query:

WITH RECURSIVE date_view(start_date, end_date) 
AS ( VALUES ('2015-07-20'::date, '2015-07-24'::date) 
     UNION ALL SELECT start_date::date + 1, end_date 
     FROM date_view 
     WHERE start_date < end_date ) 
SELECT start_date, count(date) 
FROM date_view LEFT JOIN my_table ON date=start_date 
GROUP BY date, start_date 
ORDER BY start_date ASC;

I'm having trouble translating this raw query to Django ORM query.

It would be great if someone can give a sample ORM query with/without a workaround for Common Table Expressions using PostgreSQL as database backend.

The simple reason is quoted here:

My preference is to do as much data processing in the database, short of really involved presentation stuff. I don't envy doing this in application code, just as long as it's one trip to the database

As per this answer django doesn't support CTE's natively, but the answer seems quite outdated.

References:

• MySQL: Select All Dates In a Range Even If No Records Present

• WITH Queries (Common Table Expressions)

Thanks

Showy

CTEs are definitely the way to go. We do have a feature request for them as of Django 2.0: code.djangoproject.com/ticket/28919 – Schaaff

My suggest for like answer for postgresql: https://mcmap.net/q/1234283/-django-annotate-data-by-date-for-empty-result – Electroplate

Answer 1

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.

Gantlet

Answer 2

from datetime import date, timedelta
from django.db.models.functions import Trunc
from django.db.models.expressions import Value
from django.db.models import Count, DateField

# A is model

start_date = date(2022, 5, 1)
end_date = date(2022, 5, 10)

queryset_days = A.objects\
    .annotate(date=Trunc('created', 'day', output_field=DateField())) \
    .filter(date__gte=start_date, date__lte=end_date) \
    .values('date')\
    .annotate(count=Count('id'))

queryset_missed_days = A.objects\
     .extra(select={
          'created': 'unnest(Array[%s]::date[])' %
          ','.join(map(lambda d: "'%s'::date" % d.strftime('%Y-%m-%d'),
                       set(start_date + timedelta(n)
                           for n in range((end_date - start_date).days + 1)) -
                       set(queryset_days.values_list('date', flat=True))))})\
     .annotate(count=Value(0))\
     .values('created', 'count'))

result = queryset_days.union(queryset_missed_days).order_by('date')

Electroplate

Answer 3

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

SELECT calendar, count(mt.zdate) as THE_COUNT
FROM generate_series('2015-07-20'::date
                   , '2015-07-24'::date
                   , '1 day'::interval)  calendar
LEFT JOIN my_table mt ON mt.zdate = calendar
GROUP BY 1
ORDER 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)

Chenay