#30128: Using timezone created with datetime.timedelta causes incorrect
timezone in
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.da304c19b5d1651765c2d3ff2bbfac5d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
