CODEWITHSUNDEEP
djangodatedjango-queryset
Jon Kiparsky
Jon Kiparsky

Reputation: 7743

Django: annotate QuerySet with number of seconds since object created

I have a model, call it Foo, which has an attribute created_at which notes the time (UTC) when an instance was created. I would like to annotate a query set over this model with the number of seconds since each item returned was created.

(to be more precise, I would like to "discount" items based on the time they have been open, with a configurable stale-out time, but I can do that computation if I can get the number of seconds since create)

I have tried some obvious things like

Foo.objects.annotate(time_since_create=now-F('created_at'))

and

Foo.objects.annotate(time_since_create=now.timestamp()-F('created_at'))

(where now isutc.localize(datetime.utcnow())`)

but these get me AttributeError: 'float' object has no attribute 'tzinfo', which suggests that DB arithmetic on dates is a little more complex than I'm thinking it ought to be.

Obviously, I can do this arithmetic in python, ie,

[(id, now-created_at) for id, created_at in Foo.objects.values_list("id", "created_at")]

but it would be nicer if I could get this back in the queryset.

Upvotes: 1

Views: 2254

Answers (2)

Durai
Durai

Reputation: 525

One solution you could use is extra in django queryset. You don't need to perform annotate or aggregate if you need difference for all the records.

My database is mysql. So I used TIMESTAMPDIFF in seconds. TIMESTAMPDIFF is from mysql db. You need to use the function available in your db in the below queryset. Hope this helps.

Visit https://docs.djangoproject.com/en/2.2/ref/models/querysets/#s-extra

time_since_create = {"time_since_create": """TIMESTAMPDIFF(SECOND, created_at, now())"""}
objs = Foo.objects.extra(select=time_since_create).values('name', 'time_since_create')
str(objs.query)

If you print the sql query, it will be something like this.

SELECT (TIMESTAMPDIFF(SECOND, created_at, now())) AS `time_since_create`, `foo`.`name` FROM `foo`

Upvotes: 3

ThunderHorn
ThunderHorn

Reputation: 2035

First approach

You can filter your query set like so

time_threshold =  datetime.now() - timedelta(munutes=40)
Foo.objects.filter(created_at__lte=time_threshold)

Source

output

> <QuerySet [<Foo: 2019-11-18 20:56:16.228271+00:00>, <Foo: 2019-11-18
> 20:56:31.842079+00:00>, <Foo: 2019-11-18 20:56:32.761461+00:00>, <Foo:
> 2019-11-18 20:57:04.612091+00:00>, <Foo: 20 19-11-18
> 20:57:11.401817+00:00>, <Foo: 2019-11-18 21:00:12.794631+00:00>, <Foo:
> 2019-11-18 21:00:14.935930+00:00>, <Foo: 2019-11-18
> 21:00:59.107677+00:00>, <Foo: 2019-11-18 21:01:27.5 90956+00:00>]>

Second approach

You can create a def in your model to take the time elapsed since create and then use it 

class Foo(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)

    def time_elapsed_since_create(self):
        return datetime.now(timezone.utc) - self.created_at

    def __str__(self):
        return str(self.created_at)

Output

>>> bar = Foo.objects.create()
>>> print(bar.time_elapsed_since_create())
0:00:09.480279
>>> print(bar.time_elapsed_since_create())
0:00:10.560474
>>> print(bar.time_elapsed_since_create())
0:00:11.248255

>>> print(type(bar.time_elapsed_since_create()))
<class 'datetime.timedelta'>

Function to get all of the Foos that are older than 60 seconds

>>> for i in Foo.objects.all():
...     if i.time_elapsed_since_create() > timedelta(seconds=60):
...             print(i.id, i.created_at, i.time_elapsed_since_create())
...
1 2019-11-18 20:56:16.228271+00:00 0:13:12.512974
2 2019-11-18 20:56:31.842079+00:00 0:12:56.899166
3 2019-11-18 20:56:32.761461+00:00 0:12:55.979784
4 2019-11-18 20:57:04.612091+00:00 0:12:24.129154
5 2019-11-18 20:57:11.401817+00:00 0:12:17.340419
6 2019-11-18 21:00:12.794631+00:00 0:09:15.947605
7 2019-11-18 21:00:14.935930+00:00 0:09:13.806306
8 2019-11-18 21:00:59.107677+00:00 0:08:29.634559
9 2019-11-18 21:01:27.590956+00:00 0:08:01.152280

Upvotes: 0

Related Questions