Using database functions with tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect query

[mvarnar]

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}

[Tim Graham]

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

[mvarnar]

It's about tzinfo=datetime.timezone(datetime.timedelta(...)) not tzinfo=datetime.timedelta(...).

[Can Sarıgöl]

​PR

[Tim Graham]

Tests aren't passing.

[Can Sarıgöl]

I needed help about sqlite3 date parse process. I've tried to solve it in _sqlite_datetime_parse func.

[Tim Graham]

Oracle support is missing.

[Carlton Gibson]

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

[Carlton Gibson]

New test fails on Oracle, so...

[Carlton Gibson]

OK, rebased and looking good. Thanks Can!

[Carlton Gibson]

Marking PnI since Mariusz asked for a simplification of the SQLite version. (Can, please uncheck when you've looked at that.)

[Can Sarıgöl]

Thanks Carlton

[Mariusz Felisiak <felisiak.mariusz@…>]

In fde9b7d:

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