Query Set for Max/Min Grouped by Date

Using Django Using the ORM
1

Hello All,

I have created the model listed below that is populated every hour by a temperature sensor. I would like to create a query set (or two) that gets the daily MAX and MIN temperatures.

The purpose of the query set is to use plotly to graph x (date), y1 (max temp), y2 (min temp)

I have looked at annotation, aggregation and values but still can’t figure it out. Any suggestions on the correct query set to use would be much appreciated.

class GreenHouse(models.Model):
    temp = models.FloatField(null=True, default=None)
    recorded = models.DateTimeField(auto_now_add=False)

    def __str__(self):
        return self.recorded

Thanks

2

You can use a technique similar to what’s described at Django Add field values as per month and year wise and consolidate.

You can use the TruncDate function to annotate the date on the instance, and then you can group by that date.

Side note: When posting code here, enclose the code between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This forces the forum software to keep your code properly formatted. (I’ve taken the liberty of fixing your original post for you.)

3

Thank you Ken. I really appreciate the prompt reply and thank you fixing the code format.
Here is what I came up with …

test_data = GreenHouse.objects.order_by('recorded').annotate(date=TruncDate("recorded")).values("date").annotate(max_temp=Max('temp'))

This is giving me the following snippet output after looping through test_data…

7.6 March 5, 2024
7.2 March 5, 2024
6.8 March 5, 2024
6.5 March 5, 2024
6.2 March 6, 2024
6.2 March 6, 2024
5.8 March 6, 2024
5.6 March 6, 2024
5.4 March 6, 2024
5.4 March 6, 2024
5.2 March 6, 2024

I was expecting a Max temp per day which it is not doing, Any other suggestions?

4

You need to annotate the TruncDate before the order_by, and use the order_by and values on the annotated field.

5

That did it. Here is the final query …

test_data = GreenHouse.objects.annotate(date=TruncDate("recorded")).order_by('date').values("date").annotate(max_temp=Max('temp'))

Thank you for your help!

Marc