I wanted to average the data per 5 minutes intervals at column J. But my query doesn’t average the data in the past 5 minutes, it’s doing the opposite for example:

Sample data:

-- This is just a sample data from my SO question...
DECLARE @MyTableVar TABLE (
    [TE-01] FLOAT,
    [TE-02] FLOAT,
    [TE-03] FLOAT,
    [TE-04] FLOAT,
    [TE-05] FLOAT,
    [TE-06] FLOAT,
    [TE-07] FLOAT,
    [timestamp] DATETIME
)

INSERT INTO @MyTableVar ([TE-01], [TE-02], [TE-03], [TE-04], [TE-05], [TE-06], [TE-07], [timestamp])
VALUES
('25.1', '25.7', 24.9, 26.2, 25.9, 0, 0, '2024-06-19 23:45:52.940'),
('1', '0.6', -0.6, 0, 30.9, 0, 0, '2024-06-19 23:47:23.203'),
('25.1', '25.7', 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:05.867'),
('25.1', '25.7', 25, 26.3, 25.9, 0, 0, '2024-06-19 23:51:18.927'),
('0.8', '0.5', -0.4, 0.8, 32.2, 0, 0, '2024-06-19 23:52:35.317'),
('25.1', '25.7', 25, 26.3, 26, 0, 0, '2024-06-19 23:56:30.233'),
('1.2', '0.7', 0.2, 0.9, 31.8, 0, 0, '2024-06-19 23:57:47.440'),
('0.8', '0.4', -0.5, 0.6, 32.5, 0, 0, '2024-06-20 00:05:09.667'),
('25.2', '25.7', 25.1, 26.4, 26, 0, 0, '2024-06-20 00:05:11.447'),
('0.8', '0.4', -0.5, 0.7, 32.6, 0, 0, '2024-06-20 00:05:22.670');

Column Legend:

• Column “J”: UTC +0 timestamp
• Column “K”: UTC +08 timestamp

What I want:

vs

My result

What’s happening is that instead of aggregating the from the past 5 minutes, the query is aggregating the data ahead until it hits the next 5 minute mark. Here’s the query responsible for the aggregation of the data in 5 minute intervals:

DECLARE @hours INT = 24 

/*
UTC+0 timestamp IS converted TO UTC+08 so it is convinient to look at 
for the users living on that timezone...
*/
DECLARE @USER_TIMEZONE_OFFSET VARCHAR(10) = '+08:00'
DECLARE @CURRENT_DATE DATETIME = CAST(SWITCHOFFSET(TODATETIMEOFFSET(GETDATE() ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME)

DECLARE @minutes INT = (@hours * 60)

/*
This is the main select statement, the averaging and aggregation of data at 5 minute 
intervals starts here...
*/
SELECT
    T1 = ROUND(AVG([TE-01]), 1),
    T2 = ROUND(AVG([TE-02]), 1),
    T3 = ROUND(AVG([TE-03]), 1),
    T4 = ROUND(AVG([TE-04]), 1),
    T5 = ROUND(AVG([TE-05]), 1),
    T6 = ROUND(AVG([TE-06]), 1),
    T7 = ROUND(AVG([TE-07]), 1),
    [TS] = dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0)
FROM @MyTableVar
WHERE 
    DATEDIFF(MINUTE, CAST(SWITCHOFFSET(TODATETIMEOFFSET([timeStamp] ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME), @CURRENT_DATE) >= 0
    AND DATEDIFF(MINUTE, CAST(SWITCHOFFSET(TODATETIMEOFFSET([timeStamp] ,'+00:00'), @USER_TIMEZONE_OFFSET) AS DATETIME), @CURRENT_DATE) <= @minutes
GROUP BY dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0)
ORDER BY dateadd(minute, datediff(minute, 0, SWITCHOFFSET(TODATETIMEOFFSET([timeStamp],'+00:00'), @USER_TIMEZONE_OFFSET)) / 5 * 5, 0) ASC

Also, see my MSSQL fiddle here. It also has the same data I have on the screenshots on that fiddle. Any help would be appreciated. Thanks!