[Answered ]-Using field in Trunc's kind property

by

0👍

✅

The following code works:

class MyTrunc(Func):
    def as_postgresql(self, compiler, connection):
        return super().as_sql(compiler, connection, function="DATE_TRUNC")

But calling this function demand to swap kind with date when passing them:

task.timers.annotate(start_of=Trunc('task__frequency__time_unit', 'start'))

I’m not sure if this implementation is vulnerable to SQL injection

👤Vitalii Korniichuk

1👍

This will not work. Indeed, Django will check the value of kind when it constructs the query, and then depending on the kind, make a different query, as we can see in the source code for MySQL [GitHub]:

def datetime_trunc_sql(self, lookup_type, sql, params, tzname):
    sql, params = self._convert_sql_to_tz(sql, params, tzname)
    fields = ["year", "month", "day", "hour", "minute", "second"]
    format = ("%Y-", "%m", "-%d", " %H:", "%i", ":%s")
    format_def = ("0000-", "01", "-01", " 00:", "00", ":00")
    if lookup_type == "quarter":
        return (
            f"CAST(DATE_FORMAT(MAKEDATE(YEAR({sql}), 1) + "
            f"INTERVAL QUARTER({sql}) QUARTER - "
            f"INTERVAL 1 QUARTER, %s) AS DATETIME)"
        ), (*params, *params, "%Y-%m-01 00:00:00")
    if lookup_type == "week":
        return (
            f"CAST(DATE_FORMAT("
            f"DATE_SUB({sql}, INTERVAL WEEKDAY({sql}) DAY), %s) AS DATETIME)"
        ), (*params, *params, "%Y-%m-%d 00:00:00")
    try:
        i = fields.index(lookup_type) + 1
    except ValueError:
        pass
    else:
        format_str = "".join(format[:i] + format_def[i:])
        return f"CAST(DATE_FORMAT({sql}, %s) AS DATETIME)", (*params, format_str)
    return sql, params

but for other backends, like Oracle [GitHub], it is similar.

You can make a function that works for a specific dialect:

from django.db.models.expression import Func


class CustomDateTrunc(Func):
    function = 'DATE_TRUNC'

then we can work with:

from django.db.models import DateField
from django.db.models.functions import Cast

task.timers.annotate(
    start_of=Cast(
        CustomDateTrunc('start', kind='task__frequency__time_unit'),
        output_field=DateField(),
    )
)

but that will only work for some specific dialects, which is unfortunately not really the intention of the Django ORM.

👤willeM_ Van Onsem

Leave a comment