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

[Till Backhaus]

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

[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 

[Till Backhaus]

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!