基于Django模型按日期计算不同时段收入及月度收支的实现方法

Hey developers, let's walk through how to calculate income for different time periods (this month, last month, last year's same month) and build a monthly income/expense tracking feature using your existing Django Add model.

First, let's recap your model for reference: #

# models.py
from django.db import models
from django.utils import timezone

class Add(models.Model):
    income = models.IntegerField(blank=True, null=True)
    expense = models.IntegerField(default=0)
    date = models.DateTimeField(default=timezone.now, null=True, blank=True)

1. Calculate Income for Specific Time Periods #

We'll use Django's ORM date lookups and aggregation functions to get these totals.

This Month's Income #

Filter records where the date falls within the current year and month, then sum the income values:

from django.utils import timezone
from django.db.models import Sum

today = timezone.now()
current_year = today.year
current_month = today.month

this_month_income = Add.objects.filter(
    date__year=current_year,
    date__month=current_month
).aggregate(total_income=Sum('income'))['total_income'] or 0

• The or 0 handles cases where there are no income records this month (avoids returning None).

Last Month's Income #

We need to account for cross-year scenarios (e.g., January's last month is December of the previous year). Here are two reliable approaches:

Approach 1: Calculate start/end dates of last month

first_day_of_current_month = today.replace(day=1)
last_day_of_last_month = first_day_of_current_month - timezone.timedelta(days=1)
first_day_of_last_month = last_day_of_last_month.replace(day=1)

last_month_income = Add.objects.filter(
    date__gte=first_day_of_last_month,
    date__lte=last_day_of_last_month
).aggregate(total_income=Sum('income'))['total_income'] or 0

Approach 2: Directly compute last month's year and month

last_month = today.month - 1 if today.month > 1 else 12
last_month_year = today.year if today.month > 1 else today.year - 1

last_month_income = Add.objects.filter(
    date__year=last_month_year,
    date__month=last_month
).aggregate(total_income=Sum('income'))['total_income'] or 0

Last Year's Same Month Income #

If you mean the income from the same month last year:

last_year = current_year - 1
last_year_same_month_income = Add.objects.filter(
    date__year=last_year,
    date__month=current_month
).aggregate(total_income=Sum('income'))['total_income'] or 0

If you want the total income for the entire last year, remove the date__month filter.

2. Monthly Income & Expense Breakdown #

To group records by month and calculate total income/expense for each, use Django's values() + annotate() to aggregate data:

from django.db.models import Sum, Coalesce, F

monthly_finances = Add.objects.values(
    'date__year', 'date__month'  # Group by year and month
).annotate(
    total_income=Coalesce(Sum('income'), 0),  # Replace null sums with 0
    total_expense=Sum('expense')
).annotate(
    balance=F('total_income') - F('total_expense')  # Calculate monthly balance
).order_by('-date__year', '-date__month')  # Sort from newest to oldest

Displaying the Data in Templates #

Pass monthly_finances to your template context, then render it like this:

<table>
    <thead>
        <tr>
            <th>Year</th>
            <th>Month</th>
            <th>Total Income</th>
            <th>Total Expense</th>
            <th>Monthly Balance</th>
        </tr>
    </thead>
    <tbody>
        {% for entry in monthly_finances %}
        <tr>
            <td>{{ entry.date__year }}</td>
            <td>{{ entry.date__month }}</td>
            <td>${{ entry.total_income }}</td>
            <td>${{ entry.total_expense }}</td>
            <td>${{ entry.balance }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

Key Notes #

• Ensure your date field uses timezone.now() (which you already do) to handle timezone-aware dates correctly.
• Coalesce(Sum('income'), 0) ensures we never get None for income totals (even if there are no income entries in a month).
• The order_by clause ensures the most recent months appear first in your breakdown.

内容的提问来源于stack exchange，提问作者Muhammed Bilal