Easy substraction of year’s values

I do have the following database table containing the timestamps in unix format as well as the total yield (summing up) of my solar panels every 5 mins:

| Timestamp  | TotalYield |
|------------|------------|
| 1321423500 | 1          |
| 1321423800 | 5          |
| ...        |            |
| 1573888800 | 44094536   |

Now I would like to calculate how much energy was produced each year. I thought of reading the first and last timestamp using UNION of each year:

SELECT strftime('%d.%m.%Y',datetime(TimeStamp,'unixepoch')), TotalYield FROM PascalsDayData WHERE TimeStamp IN (
    SELECT MAX(TimeStamp) FROM PascalsDayData GROUP BY strftime('%Y', datetime(TimeStamp, 'unixepoch'))
 UNION
    SELECT MIN(TimeStamp) FROM DayData GROUP BY strftime('%Y',datetime(TimeStamp,'unixepoch')) 
)

This works fine but I need to do some post processing to substract end year’s value with the first year’s one. There must be a more elegant way to do this in SQL, right?

Thanks,

Anton

Answer

You can aggregate by year and subtract the min and max value:

SELECT MAX(TotalYield) - MIN(TotalYield)
FROM PascalsDayData
GROUP BY strftime('%Y', datetime(TimeStamp, 'unixepoch'))

This assumes that TotalYield does not decrease — which your question implies.

If you actually want the next year’s value, you can use LEAD():

SELECT (LEAD(MIN(TotalYield), 1, MAX(TotalYield) OVER (ORDER BY MIN(TimeStamp) - 
        MIN(TotalYield)
       )
 FROM PascalsDayData
GROUP BY strftime('%Y', datetime(TimeStamp, 'unixepoch'))