Generating a timeseries and aggregation/annotation using QuerySet.extra()

[Wannabe Coder]

Hello everyone!

I already created a ticket detailing my use case, and one of the developers pointed me to this discussion group. I am not quite satisfied with the response I got since the DateTransforms that will be released in Django 1.9 only appear to extract year/month/day/etc numbers and do not seem to be able to annotate based on generated time periods from a series of dates/datetimes. Basically what I can do with .extra() right now is generate a table like the ones shown in my screenshots.

I hope that behavior would be possible to customize or even include in Django 1.9 out of the box. Thanks!

[Tom Evans]

I think you have slightly misunderstood. The Expressions and Transform
API allows *you* to create user defined expressions and
transformations. Yes, there are some new built in expressions and
transformations available, but the real power is allowing you to
define such a transformation in a reusable way, rather than hacking it
in to extra() like you currently have to do in each place you want to
use it.

So you want an expression that is like MonthTransform, but using
DATE_TRUNC() instead of MONTH()? Have at it! It's a documented API:

https://docs.djangoproject.com/en/dev/ref/models/lookups/#transform-reference

The DB backends operations class even support a datetime_trunc_sql()
(compare with the implementation of DateTransform, base class of
MonthTransform)

https://github.com/django/django/blob/master/django/db/models/fields/__init__.py#L2432

Cheers

Tom

[Wannabe Coder]

Alright I get it now! Thanks for the clarification! From the looks of it, being able to manipulate lhs and rhs can cover a lot of possible SQL statements in a very reusable way, though it will still be nice to have that kind of transform/expression available out-of-the-box, since I think it is a common use case. Any idea if the Django team will include that?