I created a custom Postgresql function that performs some calculations and determines the beginning of a month by using the date_trunc function. When developing the custom function, I checked it from within a database connection established with psql which worked just fine. Though such a connection I get the following output:
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)
Which is exactly what I want: It gives me the beginning of the month at my timezone. If I try to do the same from within a django shell (I am using the psycopg2 connector), I get a different result:
>>> 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'
As you can see, the database returns the beginning of the month at UTC, resulting in a two hour offset to the actual timestamp I wanted to have.
This is a result of django setting the database connection's timestamp to UTC which results in date_trunc truncating the month at UTC time.
I tried setting the timezone of the connection manually by using SET TIMEZONE TO 'Europe/Berlin', but this results in an AssertionError("database connection isn't set to UTC").
The following workaround does work in my case but it is not really a pretty solution. It only works since I do not need any return values from my function.
cursor.execute("SET TIMEZONE to 'Europe/Berlin'; my_function(parameters); SET TIMEZONE to 'UTC';")
I am hoping that somebody can help me figure out a more elegant way to solve the problem.
