How to group by date from timestamp field with Django ORM?

Question

I have this MySQL specific query

SELECT SUM(trip_amount) as tp , DATE(FROM_UNIXTIME(request_time))  as timestamp 
FROM trip_master 
WHERE trip_master.date > 1493836200 
AND trip_master.date < 1493922600 
AND trip_master.id = 6  
GROUP BY timestamp

Implemented this query in Django,

Trip.objects.filter(id=userid,date__gt = start,date__lt = end).annotate(trip_amount = Sum('trip_amount')).extra({'timestamp':'DATE(FROM_UNIXTIME(request_time))'})

As defined I have to convert time stamp to date to have grouping from dates. Has reached at the almost level but not getting group by dates. Any help is much appreciated

Answer 1

Try:

from django.db.models.expressions import RawSQL  

Trip.objects.filter(
    id=userid,
    date__gt=start,
    date__lt=end
).annotate(
    timestamp=RawSQL('DATE(FROM_UNIXTIME(request_time))', [])
).values(
    'timestamp'
).annotate(
    trip_amount=Sum('trip_amount')
)

See Group objects by dates