Take average in SQL every 2 hours-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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')

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)