What query will return consumption per hour from SQLite data with cumulative value?

Firstly, I am almost a complete novice when it comes to SQL, so I am painfully aware I probably need to be spoon-fed anything but the simplest queries. I have been searching a lot, but most relevant examples I find seem to be too complex or general to help me with my problem.

I have the following table in a SQLite database:

CHANGE_DATE	NEW_VALUE
2022-01-20 01:21:53	341
2022-01-20 01:11:55	341
2022-01-19 23:05:16	341
2022-01-19 22:55:08	340
2022-01-19 22:45:08	340
2022-01-19 22:41:02	340
2022-01-19 20:38:46	339
2022-01-19 20:35:13	339
2022-01-19 20:25:15	339
2022-01-19 20:15:08	338
2022-01-19 18:11:42	338
2022-01-19 18:05:10	338

What I would like to do is have a query that returns the total change per hour. Mathematically I can subtract the highest value from each hour from the highest value from previous hour or calculate the difference between highest/lowest value within each hour (and also per day in my next step).

I have tried to understand and modify a lot of examples with somewhat similar requirements, but at this point I do not know where to start. Any help greatly appreciated.

Answer

use some ORMs which is related to your language skill

javascript : mock c# : dapper , entity framework python : sqlallschemi java : spring …

---

So if you want pure SQL query i hope below code help you :

this code show you the max of new_value of each hour to you :

select Max(new_value) as max_value , strftime('%Y-%m-%d %H', 
change_date) as _date
from [table_name] 
GROUP BY strftime('%H', change_date),strftime('%j', change_date)
ORDER by _date DESC

and this code will show you the total change per hour between different values in an hour:

select Max(new_value)-Min(new_value) as total_change_per_hour , strftime('%Y-%m-%d %H', change_date) as _date
from [table_name] 
GROUP BY strftime('%H', change_date),strftime('%j', change_date)
ORDER by _date DESC

if you want it per day just change it to this code :

select Max(new_value)-Min(new_value) as total_change_per_hour , strftime('%Y-%m-%d %H', change_date) as _date
from [table_name] 
GROUP BY strftime('%j', change_date)
ORDER by _date DESC

you should replace [table_name] with your table name

you can learn more about working with times in sql by this link