Django项目仪表盘时区问题：今日/周/月统计数据无法获取

问题概况 #

在Django项目的仪表盘模块中，执行DashBoardView代码时，today_entries、week_entries和month_entries返回空QuerySet，但year_entries能正常获取数据。终端输出显示唯一的AdminWallet条目存在，但时间范围过滤后无结果，同时7天数据统计也只生成了最后一天的记录。

核心原因 #

1. 时间范围起始点带时分秒：start_of_week、start_of_month基于当前带时分秒的时间计算，比如start_of_month为2023-12-01 16:28:59+00:00，而AdminWallet的created_at可能早于该时分秒（如当月1日凌晨），导致__gte过滤不到数据。
2. 今日查询参数类型错误：today_entries用created_at__date=today，但today是datetime对象，__date需要传入date类型参数。
3. 循环缩进错误：7天数据统计的核心逻辑在循环外部，导致仅执行最后一次循环的计算。
4. 低效求和方式：通过Python循环遍历QuerySet求和，相比Django自带的aggregate方法，数据库查询次数更多、性能更低。

解决方案 #

1. 修正时间起始点的时分秒 #

将所有时间范围起始点的时分秒重置为0，确保覆盖当天/周/月的完整时间区间：

today = timezone.now()
# 重置时分秒为0，保证时间范围从当天凌晨开始
today_start = today.replace(hour=0, minute=0, second=0, microsecond=0)
start_of_week = today_start - timezone.timedelta(days=today.weekday())
start_of_month = today_start.replace(day=1)
start_of_year = today_start.replace(month=1, day=1)

2. 修正今日数据查询参数 #

将today_entries的查询参数改为today.date()，匹配__date需要的类型：

today_entries = wallet_entries.filter(created_at__date=today.date())

3. 修复循环缩进问题 #

将7天数据统计的核心逻辑放入循环内部，确保每天的数据都被计算：

seven_days_ago = today_start - timezone.timedelta(days=7)
daily_Bookings_data = []
daily_revenue_data = []

for i in range(7):
    day_start = seven_days_ago + timezone.timedelta(days=i)
    day_end = day_start + timezone.timedelta(days=1)
    
    Appointments_on_day = Appointment.objects.filter(date1__gte=day_start, date1__lt=day_end).count()
    
    revenue_on_day = wallet_entries.filter(created_at__gte=day_start, created_at__lt=day_end).aggregate(
            total_credit=Sum('credit'),
            total_debit=Sum('debit')
    )
    if revenue_on_day['total_credit'] is not None and revenue_on_day['total_debit'] is not None:
        revenue_on_day = revenue_on_day['total_credit'] - revenue_on_day['total_debit']
    else:
        revenue_on_day = 0

    daily_Bookings_data.append(Appointments_on_day)
    daily_revenue_data.append(revenue_on_day)

4. 优化收支计算逻辑 #

用Django的aggregate方法代替Python循环求和，减少数据库查询次数：

# 替换原循环求和代码
wallet_agg = wallet_entries.aggregate(
    total_credits=Sum('credit'),
    total_debits=Sum('debit')
)
total_credits = wallet_agg['total_credits'] or Decimal(0.00)
total_debits = wallet_agg['total_debits'] or Decimal(0.00)
total_balance = total_credits - total_debits

# 各时间段收益也用aggregate计算
week_agg = week_entries.aggregate(Sum('credit'), Sum('debit'))
total_revenue_week = (week_agg['credit__sum'] or Decimal(0.00)) - (week_agg['debit__sum'] or Decimal(0.00))

month_agg = month_entries.aggregate(Sum('credit'), Sum('debit'))
total_revenue_month = (month_agg['credit__sum'] or Decimal(0.00)) - (month_agg['debit__sum'] or Decimal(0.00))

year_agg = year_entries.aggregate(Sum('credit'), Sum('debit'))
total_revenue_year = (year_agg['credit__sum'] or Decimal(0.00)) - (year_agg['debit__sum'] or Decimal(0.00))

完整修正后的代码 #

from django.db.models import Sum
from django.utils import timezone
from rest_framework.views import APIView
from rest_framework.response import Response
from decimal import Decimal
from .models import User, AdminWallet, Appointment

class DashBoardView(APIView):
    def get(self, request):
        # 统计用户数量
        total_users = User.objects.count()
        total_workers = User.objects.filter(role='worker').count()

        # 聚合钱包总收支
        wallet_entries = AdminWallet.objects.all()
        wallet_agg = wallet_entries.aggregate(
            total_credits=Sum('credit'),
            total_debits=Sum('debit')
        )
        total_credits = wallet_agg['total_credits'] or Decimal(0.00)
        total_debits = wallet_agg['total_debits'] or Decimal(0.00)
        total_balance = total_credits - total_debits

        # 计算时间范围（重置时分秒为0）
        today = timezone.now()
        today_start = today.replace(hour=0, minute=0, second=0, microsecond=0)
        start_of_week = today_start - timezone.timedelta(days=today.weekday())
        start_of_month = today_start.replace(day=1)
        start_of_year = today_start.replace(month=1, day=1)

        # 过滤时间范围数据
        week_entries = wallet_entries.filter(created_at__gte=start_of_week)
        month_entries = wallet_entries.filter(created_at__gte=start_of_month)
        year_entries = wallet_entries.filter(created_at__gte=start_of_year)
        today_entries = wallet_entries.filter(created_at__date=today.date())

        # 计算各时间段收益
        week_agg = week_entries.aggregate(Sum('credit'), Sum('debit'))
        total_revenue_week = (week_agg['credit__sum'] or Decimal(0.00)) - (week_agg['debit__sum'] or Decimal(0.00))

        month_agg = month_entries.aggregate(Sum('credit'), Sum('debit'))
        total_revenue_month = (month_agg['credit__sum'] or Decimal(0.00)) - (month_agg['debit__sum'] or Decimal(0.00))

        year_agg = year_entries.aggregate(Sum('credit'), Sum('debit'))
        total_revenue_year = (year_agg['credit__sum'] or Decimal(0.00)) - (year_agg['debit__sum'] or Decimal(0.00))

        today_agg = today_entries.aggregate(Sum('credit'), Sum('debit'))
        total_revenue_today = (today_agg['credit__sum'] or Decimal(0.00)) - (today_agg['debit__sum'] or Decimal(0.00))

        # 统计预约状态
        Pending_Bookings = Appointment.objects.filter(status='Pending').count()
        Accepted_Bookings = Appointment.objects.filter(status='Accepted').count()
        Cancelled_Bookings = Appointment.objects.filter(status='Cancelled').count()
        Rejected_Bookings = Appointment.objects.filter(status='Rejected').count()

        # 7天数据统计
        seven_days_ago = today_start - timezone.timedelta(days=7)
        daily_Bookings_data = []
        daily_revenue_data = []

        for i in range(7):
            day_start = seven_days_ago + timezone.timedelta(days=i)
            day_end = day_start + timezone.timedelta(days=1)
            
            Appointments_on_day = Appointment.objects.filter(date1__gte=day_start, date1__lt=day_end).count()
            
            revenue_on_day = wallet_entries.filter(created_at__gte=day_start, created_at__lt=day_end).aggregate(
                    total_credit=Sum('credit'),
                    total_debit=Sum('debit')
            )
            if revenue_on_day['total_credit'] is not None and revenue_on_day['total_debit'] is not None:
                revenue_on_day = revenue_on_day['total_credit'] - revenue_on_day['total_debit']
            else:
                revenue_on_day = 0

            daily_Bookings_data.append(Appointments_on_day)
            daily_revenue_data.append(revenue_on_day)

        response_data = {
            "total_users": total_users,
            "total_workers": total_workers,
            "total_balance": total_balance,
            "total_revenue_week": total_revenue_week,
            "total_revenue_month": total_revenue_month,
            "total_revenue_year": total_revenue_year,
            "pending_bookings": Pending_Bookings,
            "accepted_bookings": Accepted_Bookings,
            "cancelled_bookings": Cancelled_Bookings,
            "rejected_bookings": Rejected_Bookings,
            "total_revenue_today": total_revenue_today,
            "daily_Bookings_data": daily_Bookings_data,
            "daily_revenue_data": daily_revenue_data
        }

        return Response(response_data)

内容的提问来源于stack exchange，提问作者arif 786