CODEWITHSUNDEEP
pythondjango
Iliyas_B
Iliyas_B

Reputation: 13

How to filter datetime field by date?

I have model:

class Account(models.Model):
    contract_date = models.DateTimeField()

Field contract_date stored in db in UTC timezone. I need select all accounts where contract_date__date == date. But date stored in MSK timezone (+3). For example: 

Account.objects.create(contract_date='2010-01-01 21:00:00')
Account.objects.filter(contract_date__date=date(2010, 1, 2)).all()

The query return a empty list.

Upvotes: 1

Views: 558

Answers (2)

dirkgroten
dirkgroten

Reputation: 20672

If you enabled timezones (USE_TZ = True in your settings), then django will make sure that all datetimes are saved in the UTC timezone and retrieved in the UTC timezone.

When displaying them, it will use the current timezone (settings.TIME_ZONE or another one if you activate it) to display them in the appropriate timezone. When receiving input from forms, it will convert them to UTC.

Also note that a date (2019-01-02) cannot be timezone aware, only a datetime can, since without the time, time zones don't make sense.

So in your case, you need to tell Django that you want to filter with the date in MKS timezone. You can use Trunc for that:

from django.db.models.functions import Trunc
from django.db.models import DateTimeField
import pytz
from datetime import datetime

mks_filter_day = pytz.timezone("Europe/Moscow").localize(datetime(2010, 1, 2))
Account.objects.exclude(contract_date__isnull=True).annotate(
    mks_day=Trunc('contract_date', 'day', output_field=DateTimeField(), tzinfo=pytz.timezone("Europe/Moscow")))\
    .filter(mks_day=mks_filter_day)
# or in Django 1.11 the above doesn't work due to a bug
Account.objects.exclude(contract_date__isnull=True).annotate(
    mks_day=Trunc('contract_date', 'day', output_field=DateTimeField(), tzinfo=pytz.timezone("Europe/Moscow")))\
    .filter(mks_day__contains=datetime.date(2010, 1, 2))

This tells the database to convert the datetime to MKS and truncates the day before comparing to the reference date. I make sure contract_date isn't null otherwise you get an error.

Upvotes: 1

Walucas
Walucas

Reputation: 2568

You are querying using a date without time... use datetime instead:

datetime(2010,1,2,21,0,0)

Upvotes: 0

Related Questions