SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Mytable (
    id INT PRIMARY KEY,
    timestamp BIGINT,
    value FLOAT
);
INSERT INTO Mytable (id, timestamp, value)
VALUES
    (1, 1684738800000000, 10.5),
    (2, 1684738801000000, 11.2),
    (3, 1684738802000000, 12.1),
    (4, 1684738803000000, 9.8),
    (5, 1684738804000000, 8.7),
    (6, 1684738805000000, 7.9),
    (7, 1684738806000000, 6.5),
    (8, 1684738807000000, 5.3),
    (9, 1684738808000000, 4.2),
    (10, 1684738809000000, 3.1);
10 rows affected

    with cte1 as(SELECT  DATEADD(SECOND, timestamp / 1000000, '19700101') AS dt,* FROM  Mytable)
with cte2 as(select DATEPART(second,dt)-(DATEPART(second, dt) + 0) % 3 as grp,datepart(date,dt) as date_col ,* from cte1)
select * from cte2
Msg 156 Level 15 State 1 Line 2
Incorrect syntax near the keyword 'with'.
Msg 319 Level 15 State 1 Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 155 Level 15 State 1 Line 2
'date' is not a recognized datepart option.

CREATE TABLE Mytable1 (
    timestamp BIGINT,
    logical BIT,
    data1 FLOAT,
    data2 FLOAT
);

INSERT INTO Mytable1 (timestamp, logical, data1, data2)
VALUES
    (1684742400000000, 1, 26.87999, 52.38766),
    (1684742470000000, 1, 71.90267, 79.29914),
    (1684742546000000, 1, 79.60897, 36.48757),
    (1684742567000000, 0, 31.45557, 29.45114),
    (1684742767000000, 0, 56.30168, 42.04401),
    (1684742849000000, 1, 62.5892, 93.38132),
    (1684742935000000, 0, 62.60873, 43.58172),
    (1684747935000000, 1, 18.78254, 22.58825),
    (1684747951000000, 0, 64.20769, 81.87297),
    (1684747990000000, 0, 50.5675, 6.505361),
    (1684748990000000, 1, 75.1228, 0.357473),
    (1684749047000000, 0, 90.91635, 23.92973),
    (1684749142000000, 1, 20.27754, 41.14995),
    (1684749242000000, 1, 65.03004, 42.71594),
    (1684751242000000, 1, 65.03004, 42.71594),
    (1684751342000000, 1, 65.03004, 42.71594),
    (1684752242000000, 1, 65.03004, 42.71594),
    (1684751342000000, 1, 65.03004, 42.71594)


18 rows affected

with
   cte1 as (SELECT  DATEADD(SECOND, timestamp / 1000000, '19700101') AS dt,* FROM  Mytable1),
   cte2 as (select DATEPART(hour,dt)-((DATEPART(hour, dt) + 0) % 3) as interval, cast(dt as date) as date_col,* from cte1)
select min(timestamp) as timestamp, avg(data1) as data1, avg(data2) as data2 from cte2 group by date_col,interval;


timestamp
data1
data2


1684742400000000
55.9066871428571
53.8046514285714

1684747935000000
58.6386018181818
35.4530394545454
timestamp	data1	data2
1684742400000000	55.9066871428571	53.8046514285714
1684747935000000	58.6386018181818	35.4530394545454