id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30128	Using database functions with tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect query	mvarnar	Can Sarıgöl	"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}
}}}"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	orm, postgresql, timezone, datetime		Accepted	1	0	0	0	0	0