Using field in Trunc's kind property

Question

I use PostgreSQL in my project and have three related models:

class Timer(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()
    task = models.ForeignKey(
        Task,
        models.CASCADE,
        related_name="timers",
    )

class Task(models.Model):
    name = models.CharField(max_length=64)
    wanted_duration = models.DurationField()
    frequency = models.ForeignKey(
        Frequency,
        models.CASCADE,
        related_name="tasks",
    )


class Frequency(models.Model):
    class TimeUnitChoices(models.TextChoices):
        DAY = "day", "day"
        WEEK = "week", "week"
        MONTH = "month", "month"
        QUARTER = "quarter", "quarter"
        YEAR = "year", "year"

    events_number = models.PositiveIntegerField()
    time_unit = models.CharField(max_length=32, choices=TimeUnitChoices.choices
    )

I want to get a start of timespan (day, week - a value of Frequency's time_unit) according to start date (field of Timer).

I tried execute next code: task.timers.annotate(start_of=Trunc('start', kind='task__frequency__time_unit'))

But Django doesn't accept field in kind argument of Trunc class. Error: psycopg.ProgrammingError: cannot adapt type 'F' using placeholder '%t' (format: TEXT)

If I execute a following query in raw SQL:

SELECT
DATE_TRUNC(schedules_frequency.time_unit, timers_timer.start)::date as start_of
FROM public.tasks_task
INNER JOIN public.schedules_frequency ON tasks_task.frequency_id = schedules_frequency.id
INNER JOIN public.timers_timer ON timers_timer.task_id = tasks_task.id;

Everything works as wanted. Is there workaround without using raw SQL directly in the Django project?

This will not work, the `kind` is interpreted at Python level: https://github.com/django/django/blob/59f475470494ce5b8cbff816b1e5dafcbd10a3a3/django/db/backends/mysql/operations.py#L113-L136 it thus needs to be known when *constructing* the query, not at the database level. — Willem Van Onsem, Aug 12 '23 at 12:16

score 1 · Answer 1 · answered Aug 12 '23 at 12:45

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.

Maybe we can achieve this in SQLAlchemy – Ahtisham Aug 12 '23 at 13:12 — Ahtisham, Aug 12 '23 at 13:12

score 0 · Accepted Answer · answered Aug 14 '23 at 12:11

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

Using field in Trunc's kind property

2 Answers2