Take average in SQL every 2 hours

Question

I have a SQL table with 2 fields: timestamp and Value. Below is an exemple of the data (took it from a previous post, see below):

2022-09-17 12:31:00     2
2022-09-17 13:46:00     3
2022-09-17 14:01:00     1.7
2022-09-17 14:16:00     2.3
2022-09-17 15:31:00     2
2022-09-17 16:46:00     2.5
2022-09-17 17:01:00     2.2
2022-09-17 17:16:00     2.4
2022-09-17 18:31:00     2.6
2022-09-17 19:46:00     2.6
2022-09-17 20:01:00     2.7

I am trying to take an average of the Value column every 2 hours (12:00:00 to 13:59:59, 14:00:00 to 15:59:59 ...).

For the final output, I'm looking for something like this:

2020-09-17 12:00:00    2.5
2020-09-17 14:00:00    2.0

I found this: Take Hourly Average in SQL But I don't know how to adapt it for 2 hours.

Thanks for your help

@Laurent does this help you? https://stackoverflow.com/questions/8094006/how-to-get-the-average-value-every-2-hours-in-sql-query — Enrico, Oct 03 '22 at 08:14

charlesdk · Answer 1 · 2022-10-03T08:52:34.640

2

From the link you provided, I structured the query following the table information in that link. We can have the following query, I hope it helps.

SELECT CAST(FLOOR(`timestamp`) AS DATETIME)  AS `time`, AVG(`value`) AS average
FROM times
GROUP BY  DATE(`timestamp`), FLOOR(HOUR(`timestamp`) / 2)

I tested it on MariaDB 10.3

edited Oct 03 '22 at 08:52

answered Oct 03 '22 at 08:34

charlesdk

176
1
11

Uh, a couple of minutes difference =D The problem is that you group by hour. 2005-02-16 14:00:00 and 2005-02-17 14:00:00 will be combined – Orachigami Oct 03 '22 at 08:47
I will remove my answer then :) – Orachigami Oct 03 '22 at 08:53

score 1 · Answer 2 · answered Oct 03 '22 at 10:47

you can use the following codes for your desired result:

select concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00'), avg(`timestamp`) from times 
group by concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00')

OR

SELECT concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00')  AS `time`, AVG(`value`) AS average
FROM times
GROUP BY  DATE(`timestamp`), FLOOR(HOUR(`timestamp`)/ 2)

Take average in SQL every 2 hours

2 Answers2