How to get the average of a column that is connected to a datetime column in mysql?-CodePudding

Hello I am trying to get the average of column tc that has a specific date as well as getting its another average in each different date only. Is there a mysql query for it? Here is my weatherdata table:

My current try of mysqlquery is this

select round(avg(tc),0),date_format(dtime,'%m/%d/%Y') as timeee from weatherdata where DATE(dtime) BETWEEN '2022-03-13' AND '2022-03-15';

I am trying to achieve this one using Python and Matplotlib wherein the dates in mysql is shown in the x axis of the graph and the y values that are plotted are the average of column tc in each different date.

trying to achieve this

Hopefully someone can help me please thanks. Still learning

CodePudding user response：

It sound to me like you want to have an average for each day. You probably want to use a GROUP BY clause:

SELECT
  DATE_FORMAT(dtime,'%m/%d/%Y') as timeee,
  ROUND(AVG(tc),0)
FROM
    weatherdata
WHERE
    DATE(dtime) BETWEEN '2022-03-13' AND '2022-03-15'
GROUP BY 1
ORDER BY DATE(dtime);