Hourly grouping of rows using Django

Question

I have been trying to group the results of table into Hourly format using DateTimeField.

SQL:

SELECT strftime('%H', created_on), count(*)
FROM users_test
GROUP BY strftime('%H', created_on);

This query works fine, but the corresponding Django query does not.

Django queries I've tried:

Test.objects.extra({'hour': 'strftime("%%H", created_on)'}).values('hour').annotate(count=Count('id'))
# SELECT (strftime("%H", created_on)) AS "hour", COUNT("users_test"."id") AS "count" FROM "users_test" GROUP BY (strftime("%H", created_on)), "users_test"."created_on" ORDER BY "users_test"."created_on" DESC

It adds additional group by "users_test"."created_on", which I guess is giving incorrect results.

It would be great if anyone can explain me this and provide a solution as well.

Environment:

• Python 3
• Django 1.8.1

Thanks in Advance

References (Possible Duplicates) (But None helping out):

• Grouping Django model entries by day using its datetime field
• Django - Group By with Date part alone
• Django aggregate on .extra values

Answer 1

To fix it, append order_by() to query chain. This will override model Meta default ordering. Like this:

Test
.objects
.extra({'hour': 'strftime("%%H", created_on)'})
.order_by()                                        #<------ here
.values('hour')
.annotate(count=Count('id'))

In my environment ( Postgres also ):

>>> print ( Material
         .objects
         .extra({'hour': 'strftime("%%H", data_creacio)'})
         .order_by()
         .values('hour')
         .annotate(count=Count('id'))
         .query )

  SELECT (strftime("%H", data_creacio)) AS "hour", 
         COUNT("material_material"."id") AS "count" 
    FROM "material_material" 
GROUP BY (strftime("%H", data_creacio))

Learn more in order_by django docs:

If you don’t want any ordering to be applied to a query, not even the default ordering, call order_by() with no parameters.

Side note: using extra() may introduce SQL injection vulnerability to your code. Use this with precaution and escape any parameters that user can introduce. Compare with docs:

Warning

You should be very careful whenever you use extra(). Every time you use it, you should escape any parameters that the user can control by using params in order to protect against SQL injection attacks . Please read more about SQL injection protection.