CODEWITHSUNDEEP
mysqlsqltimestampaverage
Patricia Diaz Serrano
Patricia Diaz Serrano

Reputation: 65

Make the average of values for time intervals mysql

I have this table: 

`Telegramas` (`Id`, `Fecha`, `Valor_Dispositivo`) VALUES
(1, '2017-12-14 11:06:01.6976', '5'),
(2, '2017-12-14 11:07:01.4561', '2'),
(3, '2017-12-14 11:08:01.1651', '6'),
(4, '2017-12-14 12:06:01.2146', '1'),
(5, '2017-12-14 12:40:01.7981', '9');

And I need an exit like this:

2017-12-14 11:00:00.0000 -> 4.33
2017-12-14 12:00:00.0000 -> 5

I need to make the average of the data with time intervals calculated by the date column every hour.

This is the query that I have, I am very lost. I need help.

SELECT TIME(DATE_SUB(Telegramas.Fecha,INTERVAL (MINUTE(Telegramas.Fecha)%20) MINUTE)) as tiempo, SUM(IFNULL(Telegramas.Valor_Dispositivo,0)) as suma FROM Telegramas GROUP BY Fecha ORDER BY Telegramas.ID_Auto DESC;

Upvotes: 2

Views: 1202

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: One easy method can work for you

SELECT CONCAT(DATE(Fecha), ' ', HOUR(Fecha),':00:00.0000') Fecha, 
    AVG(valor_Dispositivo) average
FROM Telegramas
GROUP BY DATE(Fecha), HOUR(Fecha)

Upvotes: 1

zarruq
zarruq

Reputation: 2465

One way could be to extract date and hour part from the timestamp and group by the resultant.

select DATE_ADD(date(fecha), INTERVAL EXTRACT(HOUR FROM fecha) HOUR) as FECHA_DATE_HOUR, 
       avg(Valor_Dispositivo) as Valor_Dispositivo
from Telegramas
group by date(fecha), EXTRACT(HOUR FROM fecha);

Result:

+---------------------+-------------------+
|   FECHA_DATE_HOUR   | Valor_Dispositivo |
+---------------------+-------------------+
| 14.12.2017 11:00:00 | 4.3333            |
| 14.12.2017 12:00:00 | 5.0000            |
+---------------------+-------------------+

DEMO

Upvotes: 2

user4936563
user4936563

Reputation: 81

I would leverage function DATE_FORMAT and transform type datetime:

DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %H:00:00');

In your example:

SELECT AVG(`Valor_Dispositivo`), DATE_FORMAT(`Fecha`, '%Y-%m-%d %H:00:00') `by_hour` 
FROM `Telegramas` 
GROUP BY 2;

Upvotes: 1

Related Questions