[Python] Django annotate with ExtractMonth and ExtractYear doesnt extract year

I have this model:

class KeyAccessLog(models.Model):
key = models.ForeignKey(
Key, related_name="access_logs", on_delete=models.CASCADE
)
path = models.CharField(max_length=255)
method = models.CharField(max_length=10)
ip_address = models.GenericIPAddressField()
created = models.DateTimeField(auto_add_now=True)

class Meta:
verbose_name = "Key Access Log"
verbose_name_plural = "Key Access Logs"
ordering = ("-pk",)
indexes = [
models.Index(
models.Index(
"key",
ExtractMonth("created"),
ExtractYear("created"),
name="key_month_year_idx",
),
),
]

def __str__(self):
return f"{self.key} - {self.path} - {self.method}"


My point is to use the declared index when filtering by key, month, and year but the query that is generated from ORM is not extracting as it does for the month.

dt_now = timezone.now()
qs = key.access_logs.filter(created__month=dt_now.month, created__year=dt_now.year)
print(qs.query)


gives me:

SELECT
"public_keyaccesslog"."id",
"public_keyaccesslog"."key_id",
"public_keyaccesslog"."path",
"public_keyaccesslog"."method",
"public_keyaccesslog"."ip_address",
"public_keyaccesslog"."created"
FROM
"public_keyaccesslog"
WHERE
(
"public_keyaccesslog"."key_id" = 1
AND EXTRACT(
MONTH
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) = 5
AND "public_keyaccesslog"."created" BETWEEN '2025-01-01 00:00:00+00:00' AND '2025-12-31 23:59:59.999999+00:00'
)
ORDER BY
"public_keyaccesslog"."id" DESC


Now I tried explicitly to annotate extracted month and year:

dt_now = timezone.now()
qs = key.access_logs.annotate(
month=ExtractMonth("created"),
year=ExtractYear("created")
).filter(month=dt_now.month, year=dt_now.year)
print(qs.query)


this gives me almost the same SQL:

SELECT
"public_keyaccesslog"."id",
"public_keyaccesslog"."key_id",
"public_keyaccesslog"."path",
"public_keyaccesslog"."method",
"public_keyaccesslog"."ip_address",
"public_keyaccesslog"."created",
EXTRACT(
MONTH
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) AS "month",
EXTRACT(
YEAR
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) AS "year"
FROM
"public_keyaccesslog"
WHERE
(
"public_keyaccesslog"."key_id" = 1
AND EXTRACT(
MONTH
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) = 5
AND "public_keyaccesslog"."created" BETWEEN '2025-01-01 00:00:00+00:00' AND '2025-12-31 23:59:59.999999+00:00'
)
ORDER BY
"public_keyaccesslog"."id" DESC


The year is never filtered by extracted value like the month, and then my index doesn't make any sense.

I would expect it to be:

SELECT
"public_keyaccesslog"."id",
"public_keyaccesslog"."key_id",
"public_keyaccesslog"."path",
"public_keyaccesslog"."method",
"public_keyaccesslog"."ip_address",
"public_keyaccesslog"."created",
EXTRACT(
MONTH
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) AS "month",
EXTRACT(
YEAR
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) AS "year"
FROM
"public_keyaccesslog"
WHERE
(
"public_keyaccesslog"."key_id" = 1
AND EXTRACT(
MONTH
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) = 5
AND EXTRACT(
YEAR
FROM
"public_keyaccesslog"."created" AT TIME ZONE 'UTC'
) = 2025
)
ORDER BY
"public_keyaccesslog"."id" DESC


Any solution for this?

Continue reading...