1

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.

Jon Kiparsky
  • 7,499
  • 2
  • 23
  • 38
  • What about `datetime.now()-F('created_at')`? Is there a reason why you want to annotate the queryset? Normally *rendering* of data is the responsibility of the template, *not* of the queryset. – Willem Van Onsem Nov 18 '19 at 20:49
  • @WillemVanOnsem in my examples, `now` is `utc.localize(datetime.utcnow())`. Editing the question to make this clear – Jon Kiparsky Nov 18 '19 at 20:52
  • But why do you use `.timestamp()`? Then you convert it to a `float`. What if you use `now - F('created_at')`? – Willem Van Onsem Nov 18 '19 at 20:53
  • @WillemVanOnsem I've tried both (see examples in the post) – Jon Kiparsky Nov 18 '19 at 20:53
  • @WillemVanOnsem the reason for annotating the queryset is because I have queryset logic downstream from this and I'd like to be able to keep it in that realm as long as possible. If this is not possible, I can make it work (as in the list comp example) but it'd be nice if I could make this work. – Jon Kiparsky Nov 18 '19 at 20:58
  • If you really want this to be in the queryset, then you'll need to let the database do the "unix seconds" conversion for you. The Django ORM provides no "extract function" for getting that format (like they do for __year, __month, etc), so you're stuck with a database-specific solution only. So, are you ok with using a DB solution? If so, which DB do you want to target? – Bill Huneke Nov 18 '19 at 21:50
  • @BillHuneke That makes sense, thanks for the info. I'm using mysql – Jon Kiparsky Nov 18 '19 at 21:56
  • 1
    In that case, you should probably test and then accept @Durai 's answer below. That should do it for you. Obviously you'll want to remove the values() call at the end if you want to have a general purpose queryset – Bill Huneke Nov 18 '19 at 22:06

2 Answers2

3

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`
Durai
  • 505
  • 4
  • 12
  • I think this is the way to go. If it were me I would drop the "values()" part at the end and I would use a more precise name since we're talking an integer (seconds) not an abstract (time). So, "seconds_since_created" is more appropriate IMHO – Bill Huneke Nov 18 '19 at 22:19
0

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
ThunderHorn
  • 1,975
  • 1
  • 20
  • 42