How to select only date part in postgres by using django ORM

Question

My backend setting as following

• Postgres 9.5
• Python 2.7
• Django 1.9

I have a table with datetime type, which named createdAt. I want to use Django ORM to select this field with only date part and group by createdAt filed.

For example, this createdAt filed may store 2016-12-10 00:00:00+0、2016-12-11 10:10:05+0、2016-12-11 17:10:05+0 ... etc。

By using Djanggo ORM, the output should be 2016-12-10、2016-12-11。The corresponding sql should similar to: SELECT date(createdAt) FROM TABLE GROUP BY createdAt.

Thanks.

Answer 1

Django provide Manager.raw() to perform raw queries on model but as raw query must contains primary key that is not useful in your case. In case Manager.raw() is not quite enough you might need to perform queries that don’t map cleanly to models, you can always access the database directly, routing around the model layer entirely by using connection. connection object represent the default database connection. So you can perform you query like.

from django.db import connection
#acquire cursor object by calling connection.cursor()
cursor = connection.cursor()
cursor.execute('SELECT created_at from TABLE_NAME GROUP BY created_at')

After executing query you can iterate over cursor object to get query result.

Answer 2

You can try that:

1. use __date operator to Filter by DateTimeField with date part: createAt__date, this is similar to __lt or __gt.
2. use annotate/Func/F to create an extra field base on createAt with only showing the date part.
3. use values and annotate, Count/Sum to create group by query.

Example code

from django.db import models
from django.db.models import Func, F, Count

class Post(models.Model):
    name = models.CharField('name', max_length=255)
    createAt = models.DateTimeField('create at', auto_now=True)

Post.objects.filter(createAt__date='2016-12-26')    # filter the date time field for specify date part                         
            .annotate(createAtDate=Func(F('createAt'), function='date'))  # create an extra field, named createAtDate to hold the date part of the createAt field, this is similar to sql statement: date(createAt) 
            .values('createAtDate')  # group by createAtDate                                     
            .annotate(count=Count('id')) # aggregate function, like count(id) in sql statement

output

[{'count': 2, 'createAtDate': datetime.date(2016, 12, 26)}]

Notice:

1. To specify each method's functionality, I have break the long statement into serval pieces, so you need to remove the carriage return when you paste it to your code.
2. When you have turned on timezone in your application, like USE_TZ = True, you need be more carefully when compare two dates in django. Timezone is matter when you making query as above.

Hope it would help. :)