CODEWITHSUNDEEP
django
Othman
Othman

Reputation: 3018

Get the total values in each month using Django QuerySet

I have this model for employee overtime hours

class Overtime(IncomeBase):
    day = models.DateField(verbose_name="Date")
    value = models.FloatField(default=1)

I need to extract the total value for each month. Now I am using a daily QuerySet in the manager.

class OvertimeManager(models.Manager):

    def daily_report(self):
        return self.values('day').annotate(hours=models.Sum('value')).order_by('-day')

However now I need a monthly report that will get the Sum of value for each month. I tried to extract the month first but then I lose the values.

Note: the month should not have the total sum for all years, so specifically I need to group by month,year

Upvotes: 2

Views: 1483

Answers (1)

Andrey Nelubin
Andrey Nelubin

Reputation: 3294

If you are using Postgresql you can do this. Ofc there is similar fuctions.

Overtime.objects.extra({'month': "to_char(day, 'Mon')", "year": "extract(year from day)"}).values('month', 'year').annotate(Sum('value'))

More info: http://www.postgresql.org/docs/7.4/static/functions-formatting.html http://www.postgresql.org/docs/9.1/static/functions-datetime.html

Or django way:

from django.db import connection

truncate_month = connection.ops.date_trunc_sql('month','day')
Overtime.objects.extra({'month': truncate_month}).values('month').annotate(Sum('value'))

I think this will help you.

Upvotes: 2

Related Questions