I have a problem. I have a table like this (example data):
| 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 |
I'am getting an average of values and I'am grouping it by date. I use:
select cast(timestamp as date) as dt, AVG(value) as avg_val
from tbl_name
group by cast(timestamp as date);
And my result looks like this:
22.01384610396165 2023-01-18T23:00:00.000Z
Is it possible to get only data without time and timezone?
CodePudding user response：
Yes, it is possible to get only the date (without time or timezone) from the timestamp. You can use the DATE() function in your query to convert the timestamp to a date. The syntax would be:
SELECT DATE(timestamp) AS dt
, AVG(value) AS avg_val
FROM tbl_name
GROUP BY DATE(timestamp);
This will return the result as:
22.01384610396165 2023-01-18