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 ?