Wednesday, July 30, 2014

Get latest timestamp+value from each group

I have a basic Django application which uses sqlite as a backend on low-end hardware (eg. Raspberry Pi). I have the RPi collecting sensor data and logging it. I am trying to get the last process value, timestamp, and associated sensor name in an efficient way, but can't seem to figure it out with the Django ORM.
class Device(models.Model):
= models.CharField(max_length=30)

class Log(models.Model):
= models.IntegerField()
= models.FloatField()

= models.ForeignKey(Device)

class Meta:
= ("timestamp", "device")

The following works, but is inefficient as it requires multiple SQL calls (one to start and then one for each device):
devices = Device.objects.all()

= []
for device in devices:
= Log.objects.filter("timestamp")
"timestamp": log.timestamp,
"value": log.value})

I have searched around and it seems the annotate() method might be able to help, but I can't seem to figure out how to get the associated value along with the max timestamp:
Log.objects.values("device__name").annotate(timestamp=Max("timestamp"))  # missing log value!
# or
Device.objects.annotate(timestamp=Max("log__timestamp"))  # missing log value!

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
To post to this group, send email to
Visit this group at
To view this discussion on the web visit
For more options, visit

No comments:

Post a Comment