Opened 3 years ago

Closed 3 years ago

#26649 closed Uncategorized (fixed)

Group by date_trunc (day, month, year) without extra

Reported by: Till Backhaus Owned by: nobody
Component: Database layer (models, ORM) Version: 1.10
Severity: Normal Keywords: Queryset.extra
Cc: josh.smeaton@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

This seems like a regression resulting from the deprecation of .extra.
Grouping things by different time ranges is relevant to many django users. It seems this cannot be done without the use of extra right now.

The closest I got was

Sales.objects.datetimes('timestamp', 'month').annotate(c=Count('id')).values('c')
(0.001) SELECT DISTINCT 
    COUNT("group_sales_sales"."id") AS "c", 
    DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC') 
FROM "group_sales_sales" 
WHERE "group_sales_sales"."timestamp" IS NOT NULL 
GROUP BY DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC') 
ORDER BY DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC') ASC; 
args=('UTC', 'UTC', 'UTC')
<QuerySet [{'c': 1}, {'c': 3}]>

Please note that the sql looks good. It's just that I cannot get the month from the annotation without django falling back to the wrong query:

Sales.objects.datetimes('timestamp', 'month').annotate(c=Count('id')).values('timestamp','c')
(0.001) SELECT DISTINCT 
    "group_sales_sales"."timestamp", 
    COUNT("group_sales_sales"."id") AS "c", 
    DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC') 
FROM "group_sales_sales" 
WHERE "group_sales_sales"."timestamp" IS NOT NULL 
GROUP BY DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC'), 
"group_sales_sales"."timestamp" ORDER BY DATE_TRUNC('month', "group_sales_sales"."timestamp" AT TIME ZONE 'UTC') ASC LIMIT 21; args=('UTC', 'UTC', 'UTC')

Out[10]: <QuerySet [{'timestamp': datetime.datetime(2016, 4, 1, 17, 19, 44, tzinfo=<UTC>), 'c': 1}, {'timestamp': datetime.datetime(2016, 5, 22, 17, 19, 39, tzinfo=<UTC>), 'c': 1}, {'timestamp': datetime.datetime(2016, 5, 22, 17, 19, 23, tzinfo=<UTC>), 'c': 1}, {'timestamp': datetime.datetime(2016, 5, 22, 17, 19, 33, tzinfo=<UTC>), 'c': 1}]>
#model:
class Sales(models.Model):
    something = models.CharField(max_length=32)
    timestamp = models.DateTimeField()

Change History (4)

comment:1 Changed 3 years ago by Simon Charette

Keywords: Queryset.extra added

comment:2 Changed 3 years ago by Josh Smeaton

Is the result you're after (not using your exact data, but the example should be clear enough):

month                count
-----------------    ---------
2016-01-01            13
2016-02-01            24

If so, Django 1.10 introduces public APIs for Trunc: https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#trunc

Your query would now be something like:

from django.db.models.functions import TruncMonth
Sales.objects
    .annotate(month=TruncMonth('timestamp'))  # Truncate to month and add to select list
    .values('month')                          # Group By month
    .annotate(c=Count('id'))                  # Select the count of the grouping
    .values('month', 'c')                     # (might be redundant, haven't tested) select month and count 

comment:3 Changed 3 years ago by Josh Smeaton

Cc: josh.smeaton@… added

comment:4 Changed 3 years ago by Till Backhaus

Resolution: fixed
Status: newclosed

Thanks that's exactly what I had in mind. I did discover Trunc later on, but I didn't figure out to continue querying after the first use of values. Marking this as fixed!

Note: See TracTickets for help on using tickets.
Back to Top