Django Queryset - extracting only date from datetime field in query (inside .value() )
django, django-models, django-queryset
Solution
At least in Django 1.10.5, you can use something like this, without `extra` and `RawSQL`:
from django.db.models.functions import Cast
from django.db.models.fields import DateField
table.objects.annotate(date_only=Cast('date', DateField()))
And for filtering, you can use `date` lookup (https://docs.djangoproject.com/en/1.11/ref/models/querysets/#date):
table.objects.filter(date__date__range=(start, end))
Problem
I want to extract some particular columns from django query models.py ``` class table id = models.IntegerField(primaryKey= True) date = models.DatetimeField() address = models.CharField(max_length=50) city = models.CharField(max_length=20) cityid = models.IntegerField(20) ``` This is what I am currently using for my query ``` obj = table.objects.filter(date__range(start,end)).values('id','date','address','city','date').annotate(count= Count('cityid')).order_by('date','-count') ``` I am hoping to have a SQL query that is similar to this ``` select DATE(date), id,address,city, COUNT(cityid) as count from table where date between "start" and "end" group by DATE(date), address,id, city order by DATE(date) ASC,count DESC; ```