Allow to set TIME_ZONE option on databases that support time zones.

[Mactory]

When using a cursor created by django.db.connection.cursor() to execute a database function on a postgresql database, the timezone of the database connection is always set to UTC instead of using the specified TIME_ZONE.
This behavior brakes postgres' date_trunc function, since it will truncate the hour to be 0 at UTC creating an offset from midnight at the local timestamp.

Example:
settings.py

TIME_ZONE = 'Europe/Berlin' # Offset +2
USE_TZ = TRUE

using date_trunc in a djago python shell:

./manage.py shell
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute("select date_trunc('month', TIMESTAMP WITH TIME ZONE '2014-09-14 04:10:00+02');")
>>> unicode(cursor.fetchone()[0])
u'2014-09-01 00:00:00+00:00'

using date_tunc directly in psql:

psql db
db=#select date_trunc('month', TIMESTAMP WITH TIME ZONE '2014-09-14 04:10:00+02');
       date_trunc       
------------------------
 2014-09-01 00:00:00+02
(1 row)

For executing custom functions for which no return value is needed, the following workaround does work:

>>> cursor.execute("SET TIMEZONE to %s;select date_trunc('month', TIMESTAMP WITH TIME ZONE '2014-09-14 04:10:00+02');SET TIMEZONE to 'UTC';", (settings.TIME_ZONE,))

[Aymeric Augustin]

This is by design and it's ​documented. See also the discussions on django-developers leading to the implementation of time zone support in Django 1.4.

You may use connection.ops.datetime_trunc_sql (a private API), or look at what it returns and reimplement it by yourself.

[Mariusz Felisiak]

Re-opened due to the comment.

[Mariusz Felisiak]

​PR

[Aymeric Augustin]

The patch is ready for review.

[Aymeric Augustin]

See comments on PR.

[Aymeric Augustin]

PR updated.

[Mariusz Felisiak <felisiak.mariusz@…>]

In ad88524:

Refs #23524 -- Unified BaseDatabaseWrapper.timezone.

There was a special case in this property to return None when the
database backend supports time zone. However, only the PostgreSQL
backend supports time zones and it never uses this property.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c06492dd:

Fixed #23524 -- Allowed DATABASESTIME_ZONE option on PostgreSQL.