Grouping entries from PostgreSQL database by their week or month based on timestamp value?-CodePudding

I have been trying to resolve a solution to this for a while now but due to my inexperience with both postgres(and SQL in general) and Django ORM I haven't been able to get a result that I can use.

I have this model:

class EventItem(models.Model):
    Id = models.CharField(max_length=200, primary_key=True)
    StartTime = models.DateTimeField(null=True)
    EndTime = models.DateTimeField(null=True)
    Duration = models.TimeField(null=True)
    Creator = models.CharField(max_length=50, null=True)

and I want to get the EventItems and group the entries by their week and/or month. So for every week starting from the earliest entry group all of the events for that week into one group.

Right now I am able to return the week number for each individual item by doing this:

weeks = EventItem.objects.annotate(week=ExtractWeek('StartTime')).values('week')

But this obviously doesn't group the results and I also need to keep the columns from the original table.

CodePudding user response：

You don't want to use ExtractWeek because that just gets an integer value of the week in the year, e.g. 44, this can cause clashes across datasets that exceed a year.

What you will want to use is TruncWeek which essentially caps a data value at the Monday of that week. You can combine this with order_by() to get your data grouped together.

from django.db.models.functions import TruncWeek

weeks = EventItem.objects.annotate(week=TruncWeek('StartTime').order_by('week')