different results with raw sql v django query

Using Django Using the ORM
1

Hello,

I have a Django model called Expenses that I would like to get the sum of the total expenses for 2023. My raw MySQL query gives different results than my Django query below. Am I doing something wrong in the Django query?

class Expenses(models.Model):
    user = models.ForeignKey(CustomUser, on_delete=models.PROTECT, blank=True,  null=True, default=1)
    expense = models.CharField(max_length=200)
    category = models.ForeignKey(ExpenseCategory, on_delete=models.PROTECT)
    expense_amount = models.DecimalField(max_digits=8, decimal_places=2)
    expense_date = models.DateTimeField(auto_now_add=False)
    expense_type = models.TextField(choices=SOURCE)
    description = models.CharField(max_length=200)
    source = models.CharField(max_length=200)
    transaction_id = models.CharField(max_length=100, default=0)

    def __str__(self):
       return self.expense

DJANGO QUERY

expense_data = Expenses.objects.filter(expense_date__year=2023).aggregate(year_expense=Sum('expense_amount'))['year_expense']

RAW MYSQL

select sum(expense_amount) from main_expenses where year(expense_date) = 2023;

Thank you

Marc

2

Assuming you have timezone support enabled, which is the default, then __year will perform a conversion of the values stored in the datetime field to the currently enabled timezone (assuming UTC stored in the database) before performing the lookup.

When USE_TZ is True, datetime fields are converted to the current time zone before filtering. This requires time zone definitions in the database.

This means that the actual filter on MySQL, assuming that your currently active timezone (e.g. `America/Chicago’ which has a utc offset of 6 hours) is not the one you have on your database, will be

WHERE expense_date BETWEEN '2023-01-01 6:00:00' AND '2023-01-01 5:59:59.999999
3

Hello Charettes,

That was the problem. Thank you so much!

Marc