Django-Postgres: how to group by DATE a datetime field with timezone enabled

Question

I am having this problem with prostgresql and django:

I have a lot of events that were created on a certain date at a certain time which is stored in a datetime field created .

I want to have aggregations based on the date part of the created field. The simplest examples is: how many event are in each day of this month?.
The created field is timezone aware. So the result should change depending on the timezone the user is in. For example if you created 2 events at 23:30 UTC time on 2017-10-02 if you view them from UTC-1 you should see them on 3rd of October at 00:30 and the totals should add for the 3rd.

I am struggling to find a solution to this problem that works with a lot of data. So doing for each day and SQL statement is not an option. I want something that translates into:

SELECT count(*) from table GROUP BY date

Now I found a solution for the first part of the problem:

from django.db import connection
truncate_date = connection.ops.date_trunc_sql('day', 'created')
queryset = queryset.extra({'day': truncate_date})
total_list = list(queryset.values('day').annotate(amount=Count('id')).order_by('day'))

Is there a way to add to this the timezone that should be used by the date_trunc_sql function to calculate the day? Or some other function before date_trunc_sql and then chain that one.

Thanks!

score 4 · Accepted Answer · answered Oct 16 '17 at 15:25

4

You're probably looking for this: timezone aware date_trunc function

However bear in mind this might conflict with how your django is configured. https://docs.djangoproject.com/en/1.11/topics/i18n/timezones/

answered Oct 16 '17 at 15:25

Krzysztof Szularz

5,151
24
35

Thanks. the links were useful and i found a way to solve my problem using `date_trunc_sql` . Also the comment in this one helped. https://stackoverflow.com/questions/25385370/django-orm-with-date-trunk-function-and-timezones – Vlad Oct 17 '17 at 10:02
I saw there was a connection.ops.datetime_trunc_sql function that takes the timezone as parameter but i could not make that work. Have any idea on how to do that? it was closing the connection afterwards, on the line `queryset = queryset.extra({'day': truncate_date})` – Vlad Oct 17 '17 at 10:04
AFAIK this particular line should not invoke SQL query. I might be wrong though. One thing I'd try would be to wrap that in a transaction. https://docs.djangoproject.com/en/1.11/topics/db/transactions/#django.db.transaction.atomic – Krzysztof Szularz Oct 17 '17 at 13:14

score 1 · Answer 2 · answered Jun 08 '20 at 23:00

Django 2.2+ supports the TruncDate database function with timezones

You can now do the following to :

import pytz

east_coast = pytz.timezone('America/New_York')
queryset.annotate(created_date=TruncDay("created", tzinfo=east_coast))
    .values("created_date")
    .order_by("created_date")
    .annotate(count=Count("created_date"))
    .order_by("-created_date")

Django-Postgres: how to group by DATE a datetime field with timezone enabled

2 Answers2