Opened 4 years ago
Closed 4 years ago
#31640 closed Bug (fixed)
Trunc() function take tzinfo param into account only when DateTimeField() are used as output_field
Reported by: | Serhii Romanov | Owned by: | David Wobrock |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | trunc timezone tz |
Cc: | David Rasch, David Wobrock | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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?
Change History (10)
comment:1 by , 4 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 4 years ago
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)
.
comment:3 by , 4 years ago
Yes, the provided patch solved this issue. I come up with a similar solution like your today.
comment:4 by , 4 years ago
Cc: | added |
---|
comment:5 by , 4 years ago
Cc: | added |
---|---|
Has patch: | set |
Owner: | changed from | to
Status: | new → assigned |
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
comment:6 by , 4 years ago
Patch needs improvement: | set |
---|
comment:7 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:8 by , 4 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
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
def date_trunc_sql(self, lookup_type, field_name):# https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNCreturn "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)settings.USE_TZ:django/db/models/functions/datetime.py
))