CODEWITHSUNDEEP
djangodjango-modelsdjango-ormdjango-database-functions
JPG
JPG

Reputation: 88659

Compare year from DateTimeField/DateField Django ORM

I have a model configuration as below,

class Foo(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()

How can I retrieve Foo instances with same Year?

Unsuccessfull try:

from django.db.models import F

Foo.objects.filter(start__year=F('end__year'))

returnd empty QuerySet

Upvotes: 1

Views: 433

Answers (1)

JPG
JPG

Reputation: 88659

The SQL query of Foo.objects.filter(start__year=F('end__year')) statement is,

SELECT "foo"."id", "foo"."start", "foo"."end" 
FROM "foo" 
WHERE django_datetime_extract('year', "foo"."start", 'UTC') = ("foo"."end")

See..? The comparison takes place between the integer (the extracted start year) and datetime (end datetime)

So, Django returned empty QuerySet.

What's the solution?

We could use ExtractYear() db function to extract Year from DateTimeField and use annotate() function to store it tempoparily. Then compare the annotated field against the year lookup.

from django.db.models import F
from django.db.models.functions import ExtractYear

Foo.objects.annotate(start_year=ExtractYear(F('start'))).filter(<b>end__year=F('start_year'))

SQL query:

SELECT 
    "foo"."id", "foo"."start", "foo"."end", 
    django_datetime_extract('year', "foo"."start", 'UTC') AS "start_year" 
FROM 
    "foo" 
WHERE 
    django_datetime_extract('year', "foo"."end", 'UTC') = (django_datetime_extract('year', "foo"."start", 'UTC'))

Upvotes: 0

Related Questions