Django Queryset - retrieve only the date from the datetime field in a request (inside .value ())

Question

I want to extract some specific columns from a django request

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 request

 obj = table.objects.filter(date__range(start,end)).values('id','date','address','city','date').annotate(count= Count('cityid')).order_by('date','-count') 

I hope the SQL query 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; 

Answer 1

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)) 

Answer 2

In the following case.

 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; 

You can use extra , where you can implement database functions.

 Table.objects.filter(date__range(start,end)).extra(select={'date':'DATE(date)','count':'COUNT(cityid)'}).values('date','id','address_city').order_by('date') 

Hope this helps you.

Thanks.