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 Tim Graham, 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: UnreviewedAccepted

Using a timedelta as the tzinfo argument to database functions looks untested so I can understand if it's not working.

comment:2 by mvarnar, 6 years ago

Summary: Using database functions with tzinfo=datetime.timedelta(...) results in an incorrect queryUsing 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:3 by Can Sarıgöl, 6 years ago

Has patch: set

comment:4 by Tim Graham, 6 years ago

Patch needs improvement: set

Tests aren't passing.

comment:5 by Can Sarıgöl, 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 Can Sarıgöl, 6 years ago

Owner: changed from nobody to Can Sarıgöl
Status: newassigned

comment:7 by Tim Graham, 6 years ago

Patch needs improvement: set

Oracle support is missing.

comment:8 by Can Sarıgöl, 6 years ago

Patch needs improvement: unset

comment:9 by Carlton Gibson, 6 years ago

Triage Stage: AcceptedReady for checkin

Bar the release note change, and the Oracle CI, this looks good to go.

Last edited 6 years ago by Carlton Gibson (previous) (diff)

comment:10 by Carlton Gibson, 6 years ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

New test fails on Oracle, so...

comment:11 by Asif Saifuddin Auvi, 6 years ago

Patch needs improvement: unset
Version: 2.12.2

comment:12 by Mariusz Felisiak, 6 years ago

Patch needs improvement: set
Version: 2.2master

comment:13 by Can Sarıgöl, 6 years ago

Patch needs improvement: unset

comment:14 by Carlton Gibson, 5 years ago

Triage Stage: AcceptedReady for checkin

OK, rebased and looking good. Thanks Can!

comment:15 by Mariusz Felisiak, 5 years ago

Triage Stage: Ready for checkinAccepted

comment:16 by Carlton Gibson, 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.)

comment:17 by Can Sarıgöl, 5 years ago

Patch needs improvement: unset

Thanks Carlton

comment:18 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In fde9b7d:

Fixed #30128 -- Fixed handling timedelta timezone in database functions.

Note: See TracTickets for help on using tickets.
Back to Top