I just recently needed to do date based aggregation at work, and I
discovered that this functionality already exists in Django 1.8 after the
query expression refactoring, but is undocumented. Your example can be
written like this:
from django.db.models import Sum
from django.db.models.expressions import Date, DateTime
from django.utils.timezone import get_current_timezone
sales_per_month = Model.objects.annotate(month=Date('mydate', 'month'))\
.values('month')\
.annotate(sales=Sum('sale'))\
.order_by('month')
# or
sales_per_month = Model.objects.annotate(month=DateTime('mydate', 'month',
get_current_timezone()))\
.values('month')\
.annotate(sales=Sum('sale'))\
.order_by('month')
I was thinking of writing some documentation for this so that users of
Django 1.8 know they can use it and how. An obvious problem however, is
that there is an error message in DateTime that refers specifically to it's
usage in QuerySet.datetimes. Maybe this could be treated as a bug, and a
patch for the error message could be backported?
This would also close https://code.djangoproject.com/ticket/10302
For 1.9 Date and DateTime could become importable from django.db.models
like all the other expression classes.
On Wednesday, March 25, 2015 at 5:24:44 AM UTC+1, Josh Smeaton wrote:
> Hi,
>
> Firstly (and least importantly) opening a PR makes it a lot easier to
> review code, especially when there are lots of commits. A [WIP] pull
> request is common and useful. If you get a chance, you should open one with
> this change.
>
> I think it's a good idea. So much so that I opened a ticket about a year
> ago: https://code.djangoproject.com/ticket/22394. You'll note some
> comments there about retaining the Year based behaviour as a `BETWEEN X and
> Y` rather than `Extract(YEAR)`. Otherwise, I think the support is rather
> positive. At a high level, your code looks fairly solid and I think would
> be a useful addition.
>
> Another thing I would really like to see is transform based annotations.
> I'm not 100% sure on whether .annotate(F('X__transform')) is supported or
> not, but if it is, we'll get some really nice behaviour from the use of
> transforms.
>
> Think:
>
> sales_per_month = Model.objects.annotate(month=F('mydate__month')).values(
> 'month').Aggregate(sales=Sum('sale'))
>
> If Transforms don't yet work with annotate, that'll probably be what I'd
> like to implement next. But the first step is having transforms to work
> with, where date based transforms are (to me) the most useful.
>
> Cheers,
>
> On Wednesday, 25 March 2015 13:39:39 UTC+11, Jon Dufresne wrote:
>>
>> Hi,
>>
>> I have been spending some time learning and investigating the custom
>> lookups feature that was newly introduced in 1.7 [0]. While
>> investigating, I wanted to learn by example so I started looking
>> through the Django code. In the end, I didn't find many examples.
>> However, I did notice that there exists lookups for the year, month,
>> day, (and more) value from a database date or datetime value [1].
>> These appear to be implemented as special case "builtin" lookups and
>> not through the new lookup mechanism. Is there a specific reason for
>> this or is it historical momentum?
>>
>> I started investigating if these could be refactored to use the common
>> code path and implemented using the new lookup mechanism. To my
>> delight it was not very difficult and I now have all tests passing
>> after refactoring these lookups. Right now, this lives in a branch of
>> mine and not in a ticket or pull request. The WIP branch is located
>> at: <https://github.com/jdufresne/django/tree/date-register-lookup>
>>
>> Would this be something welcome as a ticket and pull request? While
>> there is no outward change in functionality, I see it as a beneficial
>> refactoring because with this change:
>>
>> 1. The year, month, day, etc lookups are no longer treated as special
>> cases, but instead use the common code path.
>> 2. There now exists complete and useful examples of registering new
>> lookups in the Django code itself. This might help others build more
>> lookups.
>> 3. The lookups are now limited to the correct Field types where as
>> previously this was not true. I demonstrate this with a unit test.
>>
>> If this looks like it could be a welcome change I will can go forward
>> with a typical ticket and pull request.
>>
>> Cheers,
>> Jon
>>
>> [0] https://docs.djangoproject.com/en/dev/howto/custom-lookups/
>> [1] https://docs.djangoproject.com/en/dev/ref/models/querysets/#year
>>
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/81c97093-3676-4248-8d63-3abe5c51202f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
