CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-orm
Bar Gans
Bar Gans

Reputation: 1663

Django : How to distinct by DateTimeField with ignoring the second / millisecond difference

In Django, i have a model that look like this:

class Foo(models.Model):
   ...
   created = models.DateTimeField(default=None)
   ...

And I try to query like:

q = Foo.objects.values('created').distinct()

So far everything is great.

The problem is, if I have records created by a few seconds or milliseconds apart, I want to count them as one record.

So only queries created over a minute apart will be retrieved as two separate queries. How to do it? Thanks.

EDIT: Trunc is a nice option, but didn't answer the question exactly.

Here's an example: If there are two records in DB, In the first record, the value in the created field is:

2020-02-23 12: 19: 59.000000

And in the second:

2020-02-23 12: 20: 01.000000

Trunc will retrieve two records, since they are two separate minutes.

but I want to retrieve only one record, because the time difference between the two records is less than a minute ..

Upvotes: 3

Views: 1282

Answers (3)

Bar Gans
Bar Gans

Reputation: 1663

Thank you to those who answered.

I want to add, after reading the documentation following their answers, that Django has a built-in Trunc for different types of time units (new, days, hours, minutes, etc.)

For example:

TruncMonth, TruncDay , TruncMinute

so in my case, I could use Elisha answer:

from django.db.models.functions import Trunc
Foo.objects.annotate(created_minute=Trunc('created','minute'))
 .values('created_minute').distinct()

or I could just write:

from django.db.models.functions import TruncMinute
Foo.objects.annotate(created_minute=TruncMinute('created'))
 .values('created_minute').distinct()

Another note: Trunc is a nice option, but didn't answer the question exactly.

Here's an example: If there are two records in DB, In the first record, the value in the created field is:

2020-02-23 12: 19: 59.000000

And in the second:

2020-02-23 12: 20: 01.000000

Trunc will retrieve two records, since they are two separate minutes.

but I want to retrieve only one record, because the time difference between the two records is less than a minute ..

Upvotes: 1

iklinac
iklinac

Reputation: 15738

You could use Trunc database function with minute argument

Trunc('field', 'minute')

Upvotes: 1

Elisha
Elisha

Reputation: 4951

You can truncate it to the resolution you need with Django Trunc() function:

from django.db.models.functions import Trunc    

Foo.objects.annotate(created_minute=Trunc('created','minute'))
     .values('created_minute').distinct()

Upvotes: 3

Related Questions