Django group by consecutive days (Friday, Saturday, Sunday)

Question

I have an event that occurs everyday. Here is the model

class Event(models.Model):
    date  = models.DateField()
    plays = models.IntegerField()
    viewers  = models.IntegerField()
    time = models.FloatField()

I am trying to aggregate the event by consecutive days (Friday, Saturday, Sunday).

Note: Friday and Saturday are from the same week and Sunday from the next. Using raw queries (postgres) I can use the following query to aggregate

SELECT DATE_TRUNC('week', "date")::date AS date,
       SUM(plays) AS total_plays,
       SUM(viewers) AS total_views,
       SUM(time) AS total_time
FROM  app_event
WHERE extract(isodow FROM date) IN (5,6,7)
GROUP BY date_trunc('week',"date")

However in django I can't find a way to replicate the query

queryset = Event.objects.filter(date__week_day__in=[1, 6, 7])\
   .annotate('week'=TruncWeek('date')) \
   .values('week')\
   .annotate(total_views=Sum('views'), total_plays=Sum('plays'), total_time=Sum('time')) \
   .values('week', 'total_views', 'total_time', 'total_plays')

However, this query picks Sunday, Friday and Saturday in the same week.

Why do you have a `(5,6,7)` in a raw query and `[1, 6, 7]` in an orm query? — vasi1y, Jan 15 '17 at 19:18
@vasi1y From [docs](https://docs.djangoproject.com/en/dev/ref/models/querysets/#week-day). For date and datetime fields, a ‘day of the week’ match. Allows chaining additional field lookups. Takes an integer value representing the day of week from 1 (Sunday) to 7 (Saturday). — atkawa7, Jan 15 '17 at 21:00
which `locale` do you use ? There is a way to change the [`FIRST_DAY_OF_THE_WEEK`](https://docs.djangoproject.com/en/1.10/ref/settings/#first-day-of-week) in the settings but I guess it depends on the locale you use. — AKS, Jan 16 '17 at 08:39
@AKS Note: Friday and Saturday are from the same week and Sunday from the next. Django picks Sunday, Friday and Saturday in the same week correctly. How do I make sure that django pick days in different weeks? — atkawa7, Jan 16 '17 at 16:38
Did you try changing value of `FIRST_DAY_OF_THE_WEEK` setting to 1 and check if that works? — AKS, Jan 17 '17 at 05:20

Django group by consecutive days (Friday, Saturday, Sunday)

0 Answers0