Opened 9 years ago

Last modified 4 years ago

#25534 closed New feature

Allow using datetime lookups in QuerySets aggregate calls — at Version 1

Reported by: Raúl Pedro Santos Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: josh.smeaton@…, info+coding@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Raúl Pedro Santos)

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:

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:

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 with Django's ORM 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.

Change History (1)

comment:1 by Raúl Pedro Santos, 9 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top