How to filter in django to get each date latest record

user_16082534 0
Viewed 735

Example

Record Table

id  value  created_datetime
1   10     2022-01-18 10:00:00
2   11     2022-01-18 10:15:00
3   8      2022-01-18 15:15:00
4   25     2022-01-19 09:00:00
5   16     2022-01-19 12:00:00
6   9      2022-01-20 11:00:00

I want to filter this table 'Record Table' as getting each date latest value.For Example there are three dates 2022-01-18,2022-01-19,2022-01-20 in which latest value of these dates are as follows

Latest value of each dates are (Result that iam looking to get)
id  value  created_datetime
3   8      2022-01-18 15:15:00
5   16     2022-01-19 12:00:00
6   9      2022-01-20 11:00:00

So how to filter to recieve results as the above mentioned table

django-filter django-models django
1 Answers

It can be done in two steps:

First get the latest datetime for each day and then filter the records by that.

max_daily_date_times = Record.objects.extra(select={'day': 'date( created_datetime )'}).values('day') \
        .annotate(latest_datetime=Max('created_datetime'))
records = Record.objects.filter(
        created_datetime__in=[entry["latest_datetime"] for entry in max_daily_date_times]).values("id", "value",
                                                                                                  "created_datetime")
user_69442170
Related