Django: Filter for month with timezone (after switching from SQLite to PostgreSQL)

Question

In preparation for deployment, I switched from SQLite to PostgreSQL (following this advice). Now I have some trouble filtering certain items for timestamp by month which I didn't have before with SQLite. The problem is, that I get the items of the previous month instead of the current one. Look, this is what my code basically looks like:

models.py
    class Dummy(models.Model):
        …
        timestamp = models.DateTimeField()
        …

views.py
    …
    current = datetime.date.today().replace(day=1)
    dummies = Dummy.objects.values(month=TruncMonth('timestamp')).
        filter(month__year=current.year).
        filter(month__month=current.month)
    …

I actually have several Dummy entries for the current month (which is 10), but dummies remain empty. If I replace current.month with 9, I get exact these entries. Like I said before, this is only the case with PostgreSQL. As long as I used SQLite, everything was OK.

After some research I understand where the problem apparently comes from: there seems to be a difference how these different types of databases handle the timezones, see e.g. this answer.

The timestamp of the Dummy entries is stored as UTC in the database itself. If I look at one item of dummies (which I get with 9 as mentioned above), it has month values like that:

datetime.datetime(2020, 10, 1, 0, 0, tzinfo=<DstTzInfo 'Europe/Berlin' CEST+2:00:00 DST>)

The value of current is datetime.date(2020, 10, 1), without any timezone information. So I assume the month value of dummies using PostgreSQL is somehow converted to UTC and the date changes do someting like 2020, 9, 30.

I tried a lot, but I still do not know how to solve this problem, even though it must be quite easy, I guess. Any help is appreciated.

Answer 1

I was on the right track and found the answer to my question by myself. This code works:

views.py
    from django.utils import timezone
    …
    current = timezone.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    dummies = Dummy.objects.values(month=TruncMonth('timestamp')).
        filter(month=current)

Now current is no longer date but datetime including a timezone and therefore fits perfectly to month as a filter.