django implementation group by every two hours,

[kanhaiya yadav]

Hi,

I have a model 

class XyzModel (models.Model):

   name = models.CharField(max_length=NAME_LENGTH)

   unique_id = models.CharField(max_length=NAME_LENGTH)

   info = models.CharField(max_length=NAME_LENGTH, blank=True)

   violation_time = models.DateTimeField()

I have many rows in the database. So I want to group by the results for every two hours or for every four hours.

I want the result something like

name                       violation_date                               interval                                 counts

abc                           2017-07-01                                  06:00 - 08:00                           20

xyz                           2017-07-01                                   08:00 - 10:00                           30

My query is 

XyzModel.objects

            .extra({"day": "date_trunc('hour',violation_time)"}

            .values("day")

            .order_by("day")

            .annotate(count=Count("id"))

But using this query I can only group the result by one hour interval. I want to group by more than one hour interval.

Thanks

[Tom Evans]

You realise your "day" parameter is actually hours, right?

.extra({'day', 'TRUNC(EXTRACT(hour FROM violation_time) / 2)'})

'day' will then be the 0-based index of the 2 hour periods, starting
from midnight, so a value of 5 would mean 10 AM - 12 AM.

Cheers

Tom