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?

The "culprit" if you want is some custom logic Django added for this. Indeed:

class YearExact(YearLookup, Exact):
    def get_direct_rhs_sql(self, connection, rhs):
        return "BETWEEN %s AND %s"
    
    def get_bound_params(self, start, finish):
        return (start, finish)

Which is registered as the __exact lookup for an ExtractYear object (not to be confused with YearExact thus).

It probably also does not make much sense to add a separate index for year: just adding an index on created will have (almost) the same performance, since it performs logarithmic search, and using an index on ExtractYear can result in extra bookkeeping, since a lot for records will map on 2025 probably.

If you really want to get rid of this, you could just register your own ExtractYear, like:

from django.db.models.functions.datetime import ExtractYear
from django.db.models.lookups import Exact

class MyExtractYear(ExtractYear):
  pass

MyExtractYear.register_lookup(Exact)

and then thus use MyExtractYear instead. But it is probably not worth the effort, and could even result in less efficient querying.

Back to Top