Format timestamp in Django query in values

Question

I am trying to use a Dajngo QuerySet where I have to format time_stamp field in .values ('time_stamp').

In PostgreSql I can dot like this:

GROUP BY time_stamp::date

I have to do this because in the date column the format (2018-01-22 00:00:28+01) show time as well and I want know the average of the filed only in ONE day.

So my main question how can I create a query in Django which equal with this PostgreSQL query.

SELECT avg(field) FROM table GROUP BY time_stamp::date

I have no knowledge of PostgreSQL, but `::date` looks like a database function?. Have you had a look at the `Func()` query expressions? [docs](https://docs.djangoproject.com/en/2.0/ref/models/expressions/#func-expressions). Or maybe [EXTRACT/TRUNC](https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#date-functions) will work for you? — CoffeeBasedLifeform, Feb 09 '18 at 10:24

Lotram · Accepted Answer · 2018-02-09T12:00:26.677

0

Based on that answer:

MyModel.objects.annotate(day=RawSQL('date(timestamp)', ())).values('day').annotate(Avg('price')).order_by('day')

Is that what you want? The order_by is not necessary

edited Feb 09 '18 at 12:00

answered Feb 09 '18 at 11:42

Lotram

729
6
17

Eventually I used TruncDay and it helped me, but your answer is also wotking well. – Feb 09 '18 at 12:29

Format timestamp in Django query in values

1 Answers1