How to group by week with Django ORM

Question

I am using the following SQL code to check previous sunday:

DATE_ADD(tbl.date, INTERVAL (- 1) * DAYOFWEEK(tbl.date) + 1 DAY) AS week

Could you tell me whether I can use the same thanks to django models ?

I have completely no idea how to do this.

In another words I want to group by sunday in my query

My model:

class tbl_data( models.Model ):

    date = models.DateField(verbose_name="Date")
    identifier = models.CharField(max_length=10, verbose_name="Identifier")
    value = models.FloatField(verbose_name="Value")

    def __unicode__(self):
        return str( self.date ) + '-' + str( self.identifier )

data is uploading everyday, I want to group it by week ( Sunday is my first day )

Thanks in advance,

Have you made a model yet? Start there. Then show us that code and ask the question in that context. — Stephen Rauch, Mar 05 '18 at 07:07
The official Django documentation has a page about [aggregation](https://docs.djangoproject.com/en/2.0/topics/db/aggregation/). — cezar, Mar 05 '18 at 07:20
I know that page, but do not see there any information about grouping by sunday or another day of the week — python_beg22, Mar 05 '18 at 07:52
A Django `DateField` is represented in Python by a `datetime.date` instance So in Python code you can use `date.weekday()` or `date.isoweekday()` on it. Then use the aggregation as @cezar suggested — webbyfox, Mar 05 '18 at 08:53
Possible duplicate of [Django: Query Group By Month](https://stackoverflow.com/questions/37851053/django-query-group-by-month) — cezar, Mar 05 '18 at 09:16

score 2 · Accepted Answer · answered Mar 06 '18 at 09:28

2

You didn't state which database system you're using, but I'll assume it is MySQL (or MariaDB) and provide an example based on this post.

We'll make use of the MySQL function YEARWEEK(). For that purpose will create a python class inheriting from Func:

from django.db.models import Func, IntegerField

class YearWeek(Func):
    function = 'YEARWEEK'
    template = '%(function)s(%(expressions)s)'
    output_field = IntegerField()

Now we can make a query like this:

from django.db.models import Count
from yourapp.models import tbl_data
from yourapp.filewhityearweek import YearWeek

result = tbl_data.objects.annotate(  
    yearweek=YearWeek('date')
).values('yearweek').annotate(
    count=Count('date')
).order_by('yearweek')

This will return something like this:

<QuerySet [{'yearweek': 201801, 'count': 5}, {'yearweek': 201802, 'count': 3}, {'yearweek': 201803, 'count': 2}]>

Depending on your data the result will certainly vary.

The SQL query produced by Django ORM will look like this:

SELECT
    YEARWEEK('tbl_data'.'date') AS 'yearweek',
    COUNT('tbl_data'.'date') AS 'count'
FROM 'tbl_data'
    GROUP BY YEARWEEK('tbl_data'.'date')
    ORDER BY 'yearweek' ASC

This should help you to group your entries by calender week. Using YEARWEEK should give you better overview than functions like WEEK as it is returning the year and the calender week.

answered Mar 06 '18 at 09:28

cezar

11,616
6
48
84

hey , it helps a lot , thanks a lot! one quesion, in the case of this date: 2017-12-31 it returns 201753. Is there a possibility to convert the formula to such code ? : – python_beg22 Mar 06 '18 at 10:03
YEARWEEK(ADDDATE(my_date,5-DAYOFWEEK(my_date)),3) – python_beg22 Mar 06 '18 at 10:03
or DATE_FORMAT(ADDDATE(my_date,5-DAYOFWEEK(my_date)),'%x-%v') – python_beg22 Mar 06 '18 at 10:03
it would be really thankful. and yes, i am using mysql – python_beg22 Mar 06 '18 at 10:04
The function `YEARWEEK` accepts an optional second argument called `mode`. Please check the [documentation](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week). You can play with it and adjust many things like Sunday or Monday being the starting day of the week and beyond. – cezar Mar 06 '18 at 10:07
If you want to get the starting date for the week, then please take a look at this [post](https://stackoverflow.com/questions/30364141/mysql-convert-yearweek-to-date). You could do something like this: `STR_TO_DATE(CONCAT("the week you've got", " Sunday"), "%X%V %W")`. Unfortunately I couldn't manage to write a python class for that. – cezar Mar 06 '18 at 10:44

How to group by week with Django ORM

1 Answers1