Compare year from DateTimeField/DateField Django ORM

Question

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

Answer 1

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'))