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,

Answer 1

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 we'll 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.