ORM IoT query issues

[copper dove]

Hi team, I'm looking for ideas or ways to apporach crafting a django query,

The goal is return 3 measurements a day (where we take a measurement every 5mins). Ideally a Min, Max and Average.

My current thinking is below, I did try using an F object, but the can't do a .hour on an f object. The below is my latest thinking and trinking but i'm getting an supported operand types for the //: 'Extract' and 'int'.

def get_queryset(self):
    # Retrieve the year and month from the request
    year = self.get_year()
    month = self.get_month()

    # Query temperature data for the selected month using .month filter
    qs = (
        Temperature.objects.filter(
            beacon_timestamp__year=year,  # Filter by year
            beacon_timestamp__month=month,  # Filter by month
        )
        .annotate(
            # Truncate beacon_timestamp to the nearest hour
            truncated_timestamp=TruncHour("beacon_timestamp")
        )
        .annotate(
            # Extract the hour, and use ExpressionWrapper to divide by 8
            temperature_chunk_hour=ExpressionWrapper(
                Extract("beacon_timestamp", "hour") // 8 * 8,
                output_field=IntegerField(),
            )
        )
        .annotate(
            # Create a timestamp that represents the start of each 8-hour chunk
            temperature_chunk=TruncHour("beacon_timestamp")
            - timezone.timedelta(hours=F("temperature_chunk_hour"))
        )
        .values("temperature_chunk", "beacon__id", "beacon__name")
        .annotate(
            temperature_avg=Avg("temperature"),
            temperature_min=Min("temperature"),
            temperature_max=Max("temperature"),
        )
        .order_by("beacon__id", "temperature_chunk")
    )

    return qs'''

[copper dove]

another way i've tried,

>     def get_queryset(self):
>         # Retrieve the year and month from the request
>         year = self.get_year()
>         month = self.get_month()
> 
>         # Optimized query for temperature data
>         qs = (
>             Temperature.objects.filter(
>                 beacon_timestamp__year=year,  # Filter by year
>                 beacon_timestamp__month=month,  # Filter by month
>             )
>             .annotate(
>                 # Truncate beacon_timestamp to the nearest hour and calculate 8-hour chunks
>                 temperature_chunk=TruncHour("beacon_timestamp"),
>                 temperature_chunk_hour=(Extract("beacon_timestamp", "hour") // 8) * 8,
>             )
>             .annotate(
>                 # Add calculated chunk timestamps
>                 temperature_chunk_start=F("temperature_chunk")
>                 - timezone.timedelta(hours=F("temperature_chunk_hour"))
>             )
>             .values("temperature_chunk_start", "beacon__id", "beacon__name")
>             .annotate(
>                 temperature_avg=Avg("temperature"),
>                 temperature_min=Min("temperature"),
>                 temperature_max=Max("temperature"),
>             )
>             .order_by("beacon__id", "temperature_chunk_start")
>         )
> 
>         return qs

[wind torrent]

Please format your code according to the guidance in [Sharing Code in Discord](#1306622915365703681 message ) ... in short use backticks around your code
```python
code
```

[torn wing]

Instead of using the floor operator, try using the floor database function.

from django.db.models.functions import Extract, Floor

qa.annotate(temperature_chunk_hour=Floor(Extract("beacon_timestamp", "hour") / 8)) * 8)