I have problem with query. I have data like this:
| value | timestamp |
|---|---|
| 22.12 | 2023-01-18T08:00:35.000Z |
| 22.18 | 2023-01-18T09:13:12.000Z |
| 22.15 | 2023-01-18T09:16:12.000Z |
| 22.17 | 2023-01-18T09:49:35.000Z |
| 16.12 | 2023-01-25T10:15:05.000Z |
| 26.18 | 2023-01-25T10:40:05.000Z |
| 25.52 | 2023-01-25T10:55:05.000Z |
| 19.88 | 2023-01-26T11:40:05.000Z |
| 16.12 | 2023-01-16T12:40:05.000Z |
Is it possible to write query where I can get average of values gruped by date? For example:
2023-01-18 - average: 22
2023-01-25 - average: 19
CodePudding user response：
Convert timestamp to a date value and use it in GROUP BY.
Query
select cast(timestamp as date) as dt, AVG(value) as avg_val
from tbl_name
group by cast(timestamp as date);