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.
Advertisement
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