#25534: Allow using datetime lookups in QuerySets aggregate calls
----------------------------------------------+----------------------------
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
I've been scouring the web for an answer to this but the closest I can
find is #25339, which is almost what I need but not quite, so I think I
can safely conclude that it is just not possible with the currently
available functionality and thus I'm opening this ticket to suggest adding
it.
I would like to be able to use datetime lookups in an aggregate() call on
a QuerySet.
My specific use case is this: I have a set of electricity consumption
readings, each with a datetime field (and a few others). I need to sum the
consumption and cost values grouped by month, day, year, week, etc. In
other words, I need to be able to get the total energy consumption value
and corresponding cost for each month, day, year, week, etc.
I think the code I need is something along the lines of the following:
{{{#!python
result = ElectricityReading.objects\
.filter(device__grid__building_id=1) \
.annotate(num_readings=Count('id'))\
.annotate(total_consumption=Sum('consumption'))\
.annotate(total_cost=Sum('cost'))\
.aggregate(total=Count('datetime__month'))
}}}
Right now I'm doing this with this raw SQL:
{{{#!sql
SELECT
(EXTRACT(YEAR FROM datetime)) AS reading_date_year,
(EXTRACT(MONTH FROM datetime)) AS reading_date_month,
(EXTRACT(DAY FROM datetime)) AS reading_date_day,
(EXTRACT(HOUR FROM datetime)) AS reading_date_hour,
SUM(consumption) as total,
COUNT(id) as num_readings,
SUM(cost) as total_cost,
price_id
FROM electricity_reading
WHERE device_id IN (1, 2, 3)
AND datetime >= '2015-10-01'
AND datetime <= '2015-10-10'
GROUP BY reading_date_year, reading_date_month,
reading_date_day,reading_date_hour, price_id
}}}
The part I can't seem to replicate is the {{{GROUP BY}}} clause at the
end, which is what I was expecting to be able to achieve using the
{{{aggregate(total=Count('datetime__month'))}}} but instead I get the
following error:
{{{
FieldError: Cannot resolve keyword 'datetime' into field. Choices are:
consumption, cost, created, datetime, device, device_id, id, inserted_by,
inserted_by_id, manual, modified, pf, price, price_id, varh, vph1, vph2,
vph3, wh_imp, num_readings, total_consumption, total_cost
}}}
I would love that someone would tell me I am missing something, and if
that's the case, please do! :)
Otherwise, I believe it would be beneficial to add this.
--
Ticket URL: <https://code.djangoproject.com/ticket/25534>
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/050.05f5331bc932eb9aefe769e702ba7d2d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
