Question 229896 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using field in Trunc's kind property

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search