Using Django ORM to sum annotated queries

Question

I am using Django in combination with a TimescaleDB to process energy data from pv installations. Now I would like to calculate, how much energy multiple plants have generated within a given timebucket (usually per day). The plants writing the new values in a table which looks like that:

customer	datasource	value	timestamp
1	1	1	01.05.22 10:00
1	1	5	01.05.22 18:00
1	2	3	01.05.22 09:00
1	2	9	01.05.22 17:00
1	1	5	02.05.22 10:00
1	1	12	02.05.22 18:00
1	2	9	02.05.22 09:00
1	2	16	02.05.22 17:00

Now what I would like to have is, get the overal daily gain of values (so, for each day: Last Entry Value - (minus) First Entry Value) for each customer (which means, sum the daily generated energy values from all datasource which belong to the customer).

In the above example that would be for customer 1:

• Day 01.05.22:
  • Daily Gain of Datasource 1: 5 - 1 = 4
  • Daily Gain of Datasource 2: 9 - 3 = 6
  • Overall: 4 + 6 = 10
• Day 02.05.22:
  • Daily Gain of Datasource 1: 12 - 5 = 7
  • Daily Gain of Datasource 2: 16 - 9 = 7
  • Overall: 7 + 7 = 14

The result should look like that:

customer	timestamp	value
1	01.05.22 00:00	10
1	02.05.22 00:00	14

What I have now in Code is this:

dpes = (DataPointEnergy.timescale
  .filter(source__in=datasources, time__range=(_from, _to))
  .values('source', interval_end=timebucket)
  .order_by('interval_end')
  .annotate(value=Last('value', 'time') - First('value', 'time'))
  .values('interval_end', 'value', 'source')
  )

Which gives me the following result:

{'source': 16, 'timestamp': datetime.datetime(2022, 1, 9, 0, 0, tzinfo=<UTC>), 'value': 2.0}
{'source': 17, 'timestamp': datetime.datetime(2022, 1, 9, 0, 0, tzinfo=<UTC>), 'value': 2.0}
{'source': 16, 'timestamp': datetime.datetime(2022, 1, 10, 0, 0, tzinfo=<UTC>), 'value': 2.0}
{'source': 17, 'timestamp': datetime.datetime(2022, 1, 10, 0, 0, tzinfo=<UTC>), 'value': 2.0}
{'source': 16, 'timestamp': datetime.datetime(2022, 1, 11, 0, 0, tzinfo=<UTC>), 'value': 2.0}
{'source': 17, 'timestamp': datetime.datetime(2022, 1, 11, 0, 0, tzinfo=<UTC>), 'value': 2.0}

However I would still need to group the results by timestamp and sum the value column (which I am doing now using Pandas). Is there a possibility to let the database do the work?

I tried to use another .annotate() to sum up the values but this results in the error: django.core.exceptions.FieldError: Cannot compute Sum('value'): 'value' is an aggregate