[Django] #30128: Using timezone created with datetime.timedelta causes incorrect timezone in SQL query.
Django
SQL query.
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: orm, postgresql,
Severity: Normal | timezone, datetime
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30128>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
incorrect query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Using a timedelta as the tzinfo argument to database functions looks
untested so I can understand if it's not working.
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:1>
Django
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by mvarnar):
It's about tzinfo=datetime.timezone(datetime.timedelta(...)) not
tzinfo=datetime.timedelta(...).
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:2>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10910 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:3>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
Tests aren't passing.
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:4>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
Reporter: mvarnar | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
Comment:
I needed help about sqlite3 date parse process. I've tried to solve it in
_sqlite_datetime_parse func.
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:5>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Can Sarıgöl
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:6>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
Oracle support is missing.
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:7>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:8>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
timezone, datetime | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
Comment:
Bar the release note change, this looks good to go.
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:9>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* stage: Ready for checkin => Accepted
Comment:
New test fails on Oracle, so...
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:10>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* version: 2.1 => 2.2
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:11>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* version: 2.2 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:12>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:13>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
timezone, datetime | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
Comment:
OK, rebased and looking good. Thanks Can!
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:14>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Ready for checkin => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:15>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
Marking ''PnI'' since Mariusz asked for a simplification of the SQLite
version. (Can, please uncheck when you've looked at that.)
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:16>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
Comment:
Thanks Carlton
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:17>
Django
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-------------------------------------+-------------------------------------
Reporter: mvarnar | Owner: Can
| Sarıgöl
Component: Database layer | Version: master
(models, ORM) |
Keywords: orm, postgresql, | Triage Stage: Accepted
timezone, datetime |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"fde9b7d35e4e185903cc14aa587ca870037941b1" fde9b7d]:
{{{
#!CommitTicketReference repository=""
revision="fde9b7d35e4e185903cc14aa587ca870037941b1"
Fixed #30128 -- Fixed handling timedelta timezone in database functions.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30128#comment:18>