Date() in Django query

[Galil]

Hi, 

How can I convert this SQL query into a Django query?

SELECT DATE(JoinTime) FROM table

Please keep in mind that JoinTime is in datetime format and I want it to be date.

Thanks

[Stephen J. Butler]

Assuming "obj" is an instance of a Model class for this table:

obj.jointime.date()

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1d6c122b-cf31-4cc4-b105-e9a6a9f6b344%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Charette]

Hi Galil,

In the next version of Django (1.10) you'll be able to use the TruncDate expression[1]
for this exact purpose:

MyModel.objects.annotate(join_date=TruncDate('join_time')).values_list('join_date', flat=True)

In the mean time you can simply use a Func expression[2]:

MyModel.objects.annotate(join_date=Func(F('join_time'), function='DATE')).values_list('join_date', flat=True)

Cheers,
Simon

[1] https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#django.db.models.functions.datetime.TruncDate
[2] https://docs.djangoproject.com/en/1.9/ref/models/expressions/#func-expressions

[Galil]

Thank you Simon,

This func expression what was I looking for. However, some more complex queries with the date() function still confuse me.

For example the SQL query:

select count(distinct UniqueCallID), date(JoinTime) from Table group by date(JoinTime);

I tried to write it as:

calls = Call.objects.annotate(join_date=Func(F('jointime'), function='DATE'), count=Count('uniquecallid', distinct=True)).values('join_date', 'count')

But it seems that the group by is not working as expected and the values returned are not distinct. Am I missing something?

[Simon Charette]

Hi Galil,

in this case you'll want to take a look at how `values()` and `annotate()` interact with
each others[1] in regard to GROUP BY.

The following should do:

Call.objects.annotate(
    joindate=Func(F('jointime'), function='DATE')
).values('joindate').annotate(  # values before annotate() specifies the GROUP BY
    count=Count('uniquecallid', distinct=True)
)

Cheers,
Simon

[1] https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values