CODEWITHSUNDEEP
djangopostgresqldjango-orm
mahyar
mahyar

Reputation: 571

how to group records by hour and return instances

this is my model and i am using postgresql:

class TripRequest(models.Model):
    passenger = models.ForeignKey('user.Passenger', on_delete=models.DO_NOTHING)
    beginning_point = models.PointField()
    destination_point = models.PointField()
    trip_beginning_time = models.DateTimeField()
    ...

i'm not very familiar with advance orm queries i want to group trip requests that have nearest (for example in a specific hour) beginning time and after that filter it furthermore like nearest beginning point or nearest driver.

now there are answers on SO that group the query and return the count or other information of all the group items but i want to just group the instances by hour and have a collection of instances for further filtering how can i do that?

Upvotes: 0

Views: 50

Answers (1)

Olerdrive
Olerdrive

Reputation: 323

You can use django's Trunc database function:

from django.db.models.functions import Trunc

def get_dt_stats(model):
    current_date = timezone.now().replace(hour=0, minute=0, second=0, microsecond=0)
    proxy = model.objects.filter(trip_beginning_time__gte=current_date) # Or any filter you need
    proxy = proxy.values(x=Trunc('trip_beginning_time', 'hour', tzinfo=tzlocal.get_localzone()))
    proxy = proxy.annotate(amount=Count('id'))
    return {k['x'].strftime('%Y-%m-%d %H:00'): k['amount'] for k in list(proxy)}

That function returns a dict with counts of model objects grouped by hours of trip_beginning_time

Upvotes: 1

Related Questions