CODEWITHSUNDEEP
pythondjangopostgresqlpython-3.xdjango-queryset
a_Fraley
a_Fraley

Reputation: 342

How to count items in Postgresql-database with Django ORM by DateTimeField()

We have a Django application with a Postgresql database. We have a model that contains a DateTimeField().

How do we count the items by hour per day? So that we get something like:

July 2, 2016, 4 p.m. | 10
July 2, 2016, 5 p.m. | 12
...
July 3, 2016, 4 p.m. | 7
July 3, 2016, 5 p.m. | 11

# myapp/models.py

class MyModel(models.Model):
    my_date = models.DateTimeField()

# myapp/views.py

def object_count():
    query = MyModel.objects.how_to_count_items_by_hour_per_day

Upvotes: 2

Views: 518

Answers (3)

a_Fraley
a_Fraley

Reputation: 342

So, until Django 1.10, this looks to be about the best way:

jobs = MyModel.objects.filter(my_date__lte=datetime.now()).extra({"hour": "date_trunc('hour', my_date)"}).values(
        "hour").order_by('my_date').annotate(count=Count("id"))

The above code is modified from that which is given from the blog that is linked below to group by hour from the DateTimeField()

Django Aggregation: group by day

Upvotes: 1

Vladimir Danilov
Vladimir Danilov

Reputation: 2398

Django 1.10 introduces new database functions (Extract and Trunc):

from django.db.models import DateTimeField, Count
from django.db.models.functions import Trunc

MyModel.objects.annotate(
    day_hour=Trunc('my_date', 'hour', output_field=DateTimeField())
).values('day_hour').annotate(items=Count('id'))

Upvotes: 3

Todor
Todor

Reputation: 16010

Try with:

MyModel.objects.values('my_date__date', 'my_date__hour').annotate(Count('id'))

Upvotes: 1

Related Questions