Opened 6 years ago
Closed 5 years ago
#30128 closed Bug (fixed)
Using database functions with tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect query
Reported by: | mvarnar | Owned by: | Can Sarıgöl |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | orm, postgresql, timezone, datetime |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I haven’t checked this bug with other databases, but it definitely works improperly with postgres.
Django ORM create incorrect query when I use timezone determined like "timezone(timedelta(hours=some_hours))".
"timezone(timedelta(hours=5))" in query will look like "UTC+05:00", but postgres doesn't know this timezone name and handle it as POSIX style.
"UTC" part will be interpreted as some zone abbreviation and timezone will be shifted by 5 hours to the west (positive shift is shift to the west in accordance with POSIX standart), i.e. actually timezone will be equal to UTC-5.
From https://www.postgresql.org/docs/10/datatype-datetime.html :
"In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset or STDoffsetDST, where STD is a zone abbreviation, offset is a numeric offset in hours west from UTC"
Checked with:
django==2.1.5
psycopg2==2.7.6.1
postgreSQL==10.6
Using the following example model:
class test(models.Model): class Meta: db_table = 'test_timezones' datetime = models.DateTimeField()
Sample of bug is bellow:
>>> from datetime import timezone, timedelta >>> from django.db.models.functions import ExtractWeekDay >>> from django_issues.models import test >>> from django.db.models.functions import ExtractHour >>> from pytz import timezone as pytz_timezone >>> print(test.objects.annotate(hour=ExtractHour('datetime')).values('datetime', 'hour').get()) {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=<UTC>), 'hour': 7} >>> tz = timezone(timedelta(hours=5)) >>> print(tz) UTC+05:00 >>> print(test.objects.annotate(hour=ExtractHour('datetime', tzinfo=tz)).values('datetime', 'hour').get()) {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=<UTC>), 'hour': 2} >>> print(test.objects.annotate(hour=ExtractHour('datetime', tzinfo=tz)).values('datetime', 'hour').query) SELECT "test_timezones"."datetime", EXTRACT('hour' FROM "test_timezones"."datetime" AT TIME ZONE 'UTC+05:00') AS "hour" FROM "test_timezones" >>> tz2 = pytz_timezone('Asia/Yekaterinburg') >>> print(tz2) Asia/Yekaterinburg >>> print(test.objects.annotate(hour=ExtractHour('datetime', tzinfo=tz2)).values('datetime', 'hour').get()) {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=<UTC>), 'hour': 12}
Change History (18)
comment:1 by , 6 years ago
Summary: | Using timezone created with datetime.timedelta causes incorrect timezone in SQL query. → Using database functions with tzinfo=datetime.timedelta(...) results in an incorrect query |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 6 years ago
Summary: | Using database functions with tzinfo=datetime.timedelta(...) results in an incorrect query → Using database functions with tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect query |
---|
It's about tzinfo=datetime.timezone(datetime.timedelta(...)) not tzinfo=datetime.timedelta(...).
comment:5 by , 6 years ago
Patch needs improvement: | unset |
---|
I needed help about sqlite3 date parse process. I've tried to solve it in _sqlite_datetime_parse func.
comment:6 by , 6 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 6 years ago
Patch needs improvement: | unset |
---|
comment:9 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
Bar the release note change, and the Oracle CI, this looks good to go.
comment:10 by , 6 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
New test fails on Oracle, so...
comment:11 by , 6 years ago
Patch needs improvement: | unset |
---|---|
Version: | 2.1 → 2.2 |
comment:12 by , 6 years ago
Patch needs improvement: | set |
---|---|
Version: | 2.2 → master |
comment:13 by , 6 years ago
Patch needs improvement: | unset |
---|
comment:14 by , 5 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
OK, rebased and looking good. Thanks Can!
comment:15 by , 5 years ago
Triage Stage: | Ready for checkin → Accepted |
---|
comment:16 by , 5 years ago
Patch needs improvement: | set |
---|
Marking PnI since Mariusz asked for a simplification of the SQLite version. (Can, please uncheck when you've looked at that.)
Using a timedelta as the tzinfo argument to database functions looks untested so I can understand if it's not working.