Django ORM, group by day

Question

I am trying to group products by DAY, however date_created is a datetime field.

Product.objects.values('date_created') \
               .annotate(available=Count('available_quantity'))

returns:

[
    {'date_created': datetime.datetime(2012, 4, 14, 13, 3, 6), 'available': 1},
    {'date_created': datetime.datetime(2012, 4, 14, 17, 12, 9), 'available': 1},
    ...
]

I want:

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

edit: database backend MYSQL

yes I want something like [{'today': 17}, {'yesterday': 21}, etc] — Hedde van der Heide, Apr 14 '12 at 14:03

score 44 · Accepted Answer · edited Oct 05 '20 at 08:09

44

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'))

edited Oct 05 '20 at 08:09

kiril

4,914
1
30
40

answered Apr 14 '12 at 14:38

San4ez

8,091
4
41
62

2

This was helpful but I've had to clear the default ordering with an explicit order_by() in between values() and annotate(). See https://docs.djangoproject.com/en/dev/topics/db/aggregation/#interaction-with-default-ordering-or-order-by. Thanks! – somecallitblues Mar 14 '16 at 05:03
Weird... i still have multiple fields not being grouped in the count, even with the same date... i tried the sollution bellow with to_char and changed my DB from sqlite to postgres, but when running available=Count('date_created') he is considering the datetime – Diego Vinícius Jan 04 '21 at 10:53

score 6 · Answer 2 · answered Aug 10 '18 at 00:33

6

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'))

answered Aug 10 '18 at 00:33

Zanon

29,231
20
113
126

1

The queryset returned is in a more readable format with your answer. Thanks. It is a better than the one suggested by @San4ez – Simran Jan 30 '20 at 12:04

cfedermann · Answer 3 · 2012-04-14T14:26:34.930

1

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}, ...
]

edited Apr 14 '12 at 14:26

answered Apr 14 '12 at 14:05

cfedermann

3,286
19
24

sounds close but I get a list with dates only: [datetime.datetime(2012, 4, 10, 0, 0), datetime.datetime(2012, 4, 10, 0, 0), datetime.datetime(2012, 4, 10, 0, 0), datetime.datetime(2012, 4, 10, 0, 0)] – Hedde van der Heide Apr 14 '12 at 14:12
Try .annotate() before creating the DateQuerySet using .dates. – cfedermann Apr 14 '12 at 14:27
same for me, only dates – linqu Nov 27 '13 at 17:07

score 0 · Answer 4 · answered Aug 14 '20 at 10:57

0

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'))

answered Aug 14 '20 at 10:57

ronin

1
1

score 0 · Answer 5 · answered Jan 04 '21 at 11:19

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'))

Django ORM, group by day

5 Answers5

Linked