Django Orm, Group By Day

I am trying to group products by DAY, however date_created is a datetime field. Product.objects.values('date_created') \ .annotate(available=Count('available_quantit

Solution 1:

Inspired by this question try this for MySQL

from django.db.models import Count

Product.objects.extra(select={'day': 'date( date_created )'}).values('day') \
               .annotate(available=Count('date_created'))

Solution 2:

Similar approach from San4ez's answer, but returning dates as 'YYYY-MM-DD' instead of 'datetime.datetime(YYYY, MM, DD)':

Product.objects.extra(select={'day': "TO_CHAR(date_created, 'YYYY-MM-DD')"})
               .values('day') \
               .order_by('day') \
               .annotate(available=Count('date_created'))

Solution 3:

In Django 1.4, you could use .dates('date_created', 'day') instead of .values().

Try this code snippet:

Product.objects.annotate(available=Count('available_quantity')) \
  .dates('date_created', 'day')

This should return:

[
    {'date_created': datetime.datetime(2012, 4, 14), 'available': 2}, ...
]

Solution 4:

You can try

Product.objects.extra(select={'day': "TO_CHAR(date_created, 'YYYY-MM-DD')"})\
                .values('day')\
                .order_by('day')\
                .annotate(bets=Count('date_created'))

Solution 5:

By following the answer of San4ez and the changes from Zanon

For me i had to make a workaround to work with Postgres or Sqlite, since the Count() function will consider the datetime field, doesn't matter if you going to create a "day" input just with the date because the date_created is still holding the time information, so multiple records with time on it wont be merged and counted up, after trying many things deferents i reached that way of skipping the time

Django 2.2

from django.db.models import Count

# Change date() to to_char(created_at, 'YYYY-MM-DD') if using Postgres
Product.objects.extra(select={'day': 'date( date_created )'})\
    .values('day') \
    .order_by('date_created__date')\
    .annotate(available=Count('date_created__date'))