Django ORM with date_trunk function and timezones

Question

I would like to use date_trunc SQL function but it it doesn't seem to work with timezones.

Test 1 with Django :

from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT (date_trunc('day', when_start)) AS "d", 
COUNT("stats_histo_call"."id") AS "agg"
FROM "stats_histo_call"
WHERE ("stats_histo_call"."offre_id" = 28 
AND "stats_histo_call"."when_start" BETWEEN '2014-08-04 00:00:00+02:00' and '2014-08-08 23:59:59.999999+02:00')
GROUP BY (date_trunc('day', when_start))""")

print cursor.fetchone()
print cursor.fetchone()
print cursor.fetchone()
print cursor.fetchone()
print cursor.fetchone()

The SQL query in postgres log :

2014-08-21 15:18:31 CEST LOG:  instruction : SELECT (date_trunc('day', when_start)) AS "d", COUNT("stats_histo_call"."id") AS "agg" FROM "stats_histo_call" WHERE ("stats_histo_call"."offre_id" = 28  AND "stats_histo_call"."when_start" BETWEEN '2014-08-04 00:00:00+02:00' and '2014-08-08 23:59:59.999999+02:00') GROUP BY (date_trunc('day', when_start))

Result :

(datetime.datetime(2014, 8, 4, 0, 0, tzinfo=<UTC>), 12053L)
(datetime.datetime(2014, 8, 5, 0, 0, tzinfo=<UTC>), 9533L)
(datetime.datetime(2014, 8, 6, 0, 0, tzinfo=<UTC>), 7424L)
(datetime.datetime(2014, 8, 7, 0, 0, tzinfo=<UTC>), 6588L)
(datetime.datetime(2014, 8, 8, 0, 0, tzinfo=<UTC>), 5826L)

Test 2 with psql :

psql -U user -d dbname -c "<the same SQL Query>"

The SQL query in postgres log :

2014-08-21 15:23:00 CEST LOG:  instruction : SELECT (date_trunc('day', when_start)) AS "d", COUNT("stats_histo_call"."id") AS "agg" FROM "stats_histo_call" WHERE ("stats_histo_call"."offre_id" = 28  AND "stats_histo_call"."when_start" BETWEEN '2014-08-04 00:00:00+02:00' and '2014-08-08 23:59:59.999999+02:00') GROUP BY (date_trunc('day', when_start))

Result :

           d            |  agg
------------------------+-------
 2014-08-04 00:00:00+02 | 12050
 2014-08-05 00:00:00+02 |  9535
 2014-08-06 00:00:00+02 |  7417
 2014-08-07 00:00:00+02 |  6594
 2014-08-08 00:00:00+02 |  5828
(5 lignes)

SQL Queries seem to be the same so why Django return date_trunc in UTC and not psql ?

Kieger

Django insert BEGIN; SET TIME ZONE 'UTC'; COMMIT; before executing query. If I replace manually 'UTC' by 'Europe/Paris', it works. What is the clean method to get that with a queryset ? – Samons

Solution : Specify timezone in date_trunc function => date_trunc('day', when_start AT TIME ZONE 'Europe/Paris') – Samons