SELECT temperature
FROM Temp_data
WHERE reading_time >= '$date' - INTERVAL 6 HOUR
AND reading_time <= '$date' INTERVAL 8 HOUR
| temperature | reading_time |
|---|---|
| 25.6 | 2022-01-03 18:03:05 |
| 24.9 | 2022-01-03 18:13:05 |
| 25.3 | 2022-01-03 18:23:05 |
| ... | ... |
| 29.6 | 2022-01-04 07:53:05 |
This data is only for one day. If wanting to fetch average temperature data between 18:00 - 8:00 in every day and average it in the form of the week for that month. How can I query the data to get it this way?
(EXPECT)
| Average temperature | week_start_date |
|---|---|
| 25.4 | 2021-12-01 |
| 25.5 | 2021-12-05 |
| 26.3 | 2021-12-12 |
| 26.4 | 2021-12-19 |
| 26.1 | 2021-12-26 |
CodePudding user response：
SELECT
AVG(b.temperature) AS 'Average temperature',
DATE(b.reading_time) AS 'week_start_date'
FROM (
SELECT a.temperature, a.reading_time
FROM temp_data a
WHERE TIME(a.reading_time) > '18:00:00' OR TIME(a.reading_time) < '08:00:00'
) b
GROUP BY DATE_FORMAT(b.reading_time, '%u')
Result (with random temperature data):
