#26649: Group by date_trunc (day, month, year) without extra
----------------------------------------------+--------------------
Reporter: tback | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.10
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26649>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.14a893af9431c3b94712246a21b40618%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
