Opened 22 months ago

Closed 21 months ago

Last modified 21 months ago

#21182 closed Bug (needsinfo)

Extra Column in Group By When Using date_trunc

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


I'm trying to basically sum the amount column of a bunch of financial transactions per-month by category. I seem to have gotten 90% of the way there with the ORM, but it looks like it is getting confused by my use of date_trunc. In the Group By, all I want is category_id and month (the date_trunc result), yet I also get the raw date column as well.

Here's the code I've put together for the ORM so far:

truncate_date = django.db.connection.ops.date_trunc_sql(span, "date")
print Transaction.objects.extra({span: truncate_date}).values("category", span).annotate(total=Sum("amount")).query

This results in:

SELECT (django_date_trunc('month', date)) AS "month", "finance_transaction"."category_id", SUM("finance_transaction"."amount") AS "total" FROM "finance_transaction" GROUP BY "finance_transaction"."category_id", "finance_transaction"."date", (django_date_trunc('month', date)) ORDER BY "finance_transaction"."date" DESC

Change History (3)

comment:1 Changed 21 months ago by timo

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

I'm skeptical of your use of date_trunc_sql -- this isn't a public API that's designed for general use as far as I know.

Assuming there is a bug in Django though, it would be helpful if you could include a test for Django's test suite so we can more easily reproduce the issue.

comment:2 Changed 21 months ago by timo

  • Resolution set to needsinfo
  • Status changed from new to closed

Closing as needs info in absence of a test to reproduce. Feel free to reopen if you can provide more details, thanks!

comment:3 Changed 21 months ago by shai

For anyone who looks at this later:

The query produced includes ORDER BY "finance_transaction"."date" DESC, which hints that the user's Transaction model has a meta ordering=["-date"]; if this is the case, the date field is correctly added to the group-by, to allow this ordering. Ordering by month instead should solve the problem.

Note: See TracTickets for help on using tickets.
Back to Top