Trunc() function take tzinfo param into account only when DateTimeField() are used as output_field

[Serhii Romanov]

I'm trying to use TruncDay() function like this

TruncDay('created_at', output_field=DateField(), tzinfo=tz_kyiv)

but for PostgresSQL the code are generated as

(DATE_TRUNC('day', "storage_transaction"."created_at"))

So timezone convertation like

AT TIME ZONE 'Europe/Kiev'

was totally missed from sql.

After the investigation of code I've found out that Timezone converting are applied only when output_field=DateTimeField

    def as_sql(self, compiler, connection):
        inner_sql, inner_params = compiler.compile(self.lhs)
        if isinstance(self.output_field, DateTimeField):
            tzname = self.get_tzname()
            sql = connection.ops.datetime_trunc_sql(self.kind, inner_sql, tzname)
        elif isinstance(self.output_field, DateField):
            sql = connection.ops.date_trunc_sql(self.kind, inner_sql)
        elif isinstance(self.output_field, TimeField):
            sql = connection.ops.time_trunc_sql(self.kind, inner_sql)
        else:
            raise ValueError('Trunc only valid on DateField, TimeField, or DateTimeField.')
        return sql, inner_params

Why is that? Is there any reason for it OR is it only such feature that isn't still implemented?

[Simon Charette]

It looks like the logic should be based off self.lhs.output_field instead.

Assuming you are using PostgreSQL does the following patch addresses your issue?

django/db/backends/postgresql/operations.py

@@ -38 +38 @@
         else:
             return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
 
-    def date_trunc_sql(self, lookup_type, field_name):
-        # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
-        return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
-
     def _prepare_tzname_delta(self, tzname):
         if '+' in tzname:
             return tzname.replace('+', '-')
@@ -50 +46 @@
         return tzname
 
     def _convert_field_to_tz(self, field_name, tzname):
-        if settings.USE_TZ:
+        if tzname and settings.USE_TZ:
             field_name = "%s AT TIME ZONE '%s'" % (field_name, self._prepare_tzname_delta(tzname))
         return field_name
 
@@ -71 +67 @@
         # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
         return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
 
-    def time_trunc_sql(self, lookup_type, field_name):
+    def date_trunc_sql(self, lookup_type, field_name, tzname=None):
+        field_name = self._convert_field_to_tz(field_name, tzname)
+        # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
+        return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
+
+    def time_trunc_sql(self, lookup_type, field_name, tzname=None):
+        field_name = self._convert_field_to_tz(field_name, tzname)
+        field_name = self._convert_field_to_tz(field_name, tzname)
         return "DATE_TRUNC('%s', %s)::time" % (lookup_type, field_name)
 
     def json_cast_text_sql(self, field_name):

django/db/models/functions/datetime.py

@@ -191 +191 @@
 
     def as_sql(self, compiler, connection):
         inner_sql, inner_params = compiler.compile(self.lhs)
-        if isinstance(self.output_field, DateTimeField):
+        tzname = None
+        if isinstance(self.lhs.output_field, DateTimeField):
             tzname = self.get_tzname()
+        if isinstance(self.output_field, DateTimeField):
             sql = connection.ops.datetime_trunc_sql(self.kind, inner_sql, tzname)
         elif isinstance(self.output_field, DateField):
-            sql = connection.ops.date_trunc_sql(self.kind, inner_sql)
+            sql = connection.ops.date_trunc_sql(self.kind, inner_sql, tzname)
         elif isinstance(self.output_field, TimeField):
-            sql = connection.ops.time_trunc_sql(self.kind, inner_sql)
+            sql = connection.ops.time_trunc_sql(self.kind, inner_sql, tzname)
         else:
             raise ValueError('Trunc only valid on DateField, TimeField, or DateTimeField.')
         return sql, inner_params

[Simon Charette]

It does pass the test suite so it looks like this is definitely a bug. I think we should go as far as raising an exception when Extract(tzinfo) and Trunc(tzinfo) is specified for a left hand side that doesn't have a isinstance(lhs.output_field, DateTimeField).

[Serhii Romanov]

Yes, the provided patch solved this issue. I come up with a similar solution like your today.

[David Wobrock]

Hi,

I tried to tackle this issue, starting from the what Simon suggested.

Here's the PR with more details about the approach: ​https://github.com/django/django/pull/13495

[Mariusz Felisiak <felisiak.mariusz@…>]

In a0571c10:

Refs #31640 -- Made Extract raise ValueError when using tzinfo with Date/TimeField.

[Mariusz Felisiak <felisiak.mariusz@…>]

In ee00532:

Fixed #31640 -- Made Trunc() truncate datetimes to Date/TimeField in a specific timezone.