Get MIN, MAX, AVG value with datetime for each DAY (MySQL)

Question

I have a MySQL database with lots of entrys with columns like "local_time", "temp", "wind", etc.

What I want to archive is get in just one query the MIN value for temp, the MAX value for temp in each day with the datetime for min and max.

eg:

local_time | temp

2022-12-01 12:20 | 10
2022-12-01 12:40 |20

2022-12-02 19:00 | 12
2022-12-02 20:05 | 8

2022-12-03 22:22 | 14
2022-12-03 22:30 | 16

Result I wanted for query:

date | min | min_date | max | max_date| avg

2022-12-01 | 10 | datetime | 20 | datetime | avg_value
2022-12-02 | 8 | datetime | 12 | datetime | avg_value
2022-12-03 | 14 | datetime | 16 | datetime | avg_value

For now, I'm using this query:

select DATE(local_time) as local_time, MIN(temp) as max, MAX(temp) as min, AVG(temp) as avg FROM data GROUP BY DATE(local_time);

But I need the datetime (local_time) for the MIN value and MAX value, for every single day.

Any help is welcome.

For now, I'm using this query:

select DATE(local_time) as local_time, MIN(temp) as max, MAX(temp) as min, AVG(temp) as avg FROM data GROUP BY DATE(local_time);

But I need the datetime (local_time) for the MIN value and MAX value, for every single day.

Answer 1

You say you want the time for the minimum temperature and the time for the maximum temperature per day, but rather than using singular you should use the plural. Yes, there is one minimum and maximum temperature per day, but there is not necessarily one time when that minimum resp. that maximum temperature is reached. These temperatures can occur multiple times a day. We must first determine a rule what to do in case of such ties, then find the algorithm.

Let's say we are looking for the first time the minimum was reached and the last time the maximum was reached. (We could look at well vice versa or collect all times one of the temperatures is met.)

We need two steps: First, find the minimum and maximum temperatures per day, then aggregate the data to get one row per day.

select
  local_date,
  min(temp) as min_temp,
  min(case when temp = min_temp then local_time end) as first_min_temp_time,
  max(temp) as max_temp,
  max(case when temp = max_temp then local_time end) as last_max_temp_time,
  avg(temp) as avg_temp
from
(
  select
    date(local_time) as local_date,
    time(local_time) as local_time,
    temp,
    min(temp) over (partition by date(local_time)) as min_temp,
    max(temp) over (partition by date(local_time)) as max_temp
  from data
) temperatures
group by local_date
order by local_date;

Well, if you were looking for the first time the maximum temperature is reached, that would be

min(case when temp = max_temp then local_time end) as first_max_temp_time,

of course, and if you were looking for all the times that temperature occured in the day, it would be something along the lines of

group_concat(case when temp = max_temp then time_format(local_time, '%H:%i') end
             order by local_time
             separator ', '
            ) as all_max_temp_times,

Answer 2

I din't get it working in one go so I joined two queries: a group by query to get your min, max and average and one using window functions to get mindate for min and maxdate for max:

select a.local_date, a.min, b.mindatetime , a.max, b.maxdatetime, a.avg from 
(select DATE(local_time) as local_date, 
        MIN(temp) as min,  
        MAX(temp) as max, AVG(temp) as avg
   FROM data GROUP BY DATE(local_time)) a
join 
(select distinct DATE(local_time) as local_date, 
                 first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp asc) as mindatetime,
                 first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp desc) as maxdatetime
  from data) b on a.local_date = b.local_date

https://www.db-fiddle.com/f/owZiLJuKhqc4DF6XYa3aJV/0

I guess the query with window functions leaves space for optimizing...

EDIT

Just wondered if a version only with window functions would perform better(It's nicer to look at anyway):

select distinct DATE(local_time) as local_date, 
 MIN(temp) OVER ( PARTITION BY DATE(local_time) )  as min,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp asc) as mindatetime,
 MAX(temp) OVER ( PARTITION BY DATE(local_time) )  as max,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp desc) as maxdatetime,
 AVG(temp) OVER ( PARTITION BY DATE(local_time) )  as avg
from data

https://www.db-fiddle.com/f/owZiLJuKhqc4DF6XYa3aJV/2

Answer 3

If you multiple identical Min values or MAX values for that matter, you need to adjust the subqueries

Schema (MySQL v8.0)

CREATE TABLE data (
  local_time datetime,
  temp INT
,INDEX(local_time)
);
INSERT INTO data  VALUES ('2022-12-01 12:20' , 10);
INSERT INTO data  VALUES ('2022-12-01 12:40' ,20);

INSERT INTO data  VALUES ('2022-12-02 19:00' , 12);
INSERT INTO data  VALUES ('2022-12-02 20:05' , 8);

INSERT INTO data  VALUES ('2022-12-03 22:22' , 14);
INSERT INTO data  VALUES ('2022-12-03 22:30' , 16);

---

Query #1

SELECT 
    DATE(d.local_time) AS local_time,
    MAX(d.temp) AS max,
    MAX((SELECT local_time FROM data WHERE DATE(local_time) = DATE(d.local_time) ORDEr BY temp DESC LIMIT 1)) max_datetime,
    MIN(d.temp) AS min,
    MIN((SELECT local_time FROM data WHERE DATE(local_time) = DATE(d.local_time) ORDEr BY temp ASC LIMIT 1)) min_datetime,
    AVG(d.temp) AS avg
FROM
    data d
  GROUP BY DATE(d.local_time);

local_time	max	max_datetime	min	min_datetime	avg
2022-12-01	20	2022-12-01 12:40:00	10	2022-12-01 12:20:00	15.0000
2022-12-02	12	2022-12-02 19:00:00	8	2022-12-02 20:05:00	10.0000
2022-12-03	16	2022-12-03 22:30:00	14	2022-12-03 22:22:00	15.0000

---

View on DB Fiddle