Django Queryset - extracting only date from datetime field in query (inside .value() )

Question

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;

Eugene Pakhomov · Answer 1 · 2017-06-23T22:33:36.067

25

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

edited Jun 23 '17 at 22:33

answered Mar 04 '17 at 11:46

Eugene Pakhomov

9,309
3
27
53

1

That is really nice ! I've tried to use it without keyword argument, which is possible with simple expressions like `annotate(Min('date'))`,..but with this Cast, the keyword-alias is mandatory. – smido Mar 29 '18 at 18:16
1

with Oracle I had to do `from django.db.models.functions import Trunc` and `table.objects.annotate(date_only=Trunc('date', 'day', output_field=DateField()))` – epineda Nov 29 '18 at 21:24

score 6 · Answer 2 · edited Dec 15 '14 at 22:36

6

For the below 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 DB 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 it will help you.

Thanks.

edited Dec 15 '14 at 22:36

Community

1
1

answered Nov 21 '13 at 14:15

CrazyGeek

3,397
2
24
38

2

Nowadays, [`.extra` should only be used as a last resort](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.query.QuerySet.extra). Is there any alternative? – mgalgs Jan 05 '17 at 00:20
2

I think they have mentioned its equivalent on in the above mentioned docs, As they said you should use RawSQL (https://docs.djangoproject.com/en/1.10/ref/models/expressions/#django.db.models.expressions.RawSQL). – CrazyGeek Jan 06 '17 at 02:32

Django Queryset - extracting only date from datetime field in query (inside .value() )

2 Answers2