#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.
