Aggregate Data Using UNIX Time in SQL

I’ve seen many posts already discussing how to use DATEPARTS() to aggregate data based on a datetime, but I haven’t found anything discussing this for UNIX time specifically. My goal is to be able to aggregate data whether by sec, min, hr or day, based on the initial and final timestamps provided. For example: Say my datetimes are:

tstart = datetime('2023-05-22 07:00:00','TimeZone','UTC');
tstop = datetime('2023-05-23 07:00:00','TimeZone','UTC');

These convert to:

Tstart = "1684738800000000"
Tstop = "1684825200000000"

Which is the number of microseconds that have passed. The reason for this conversion to UNIX is because the dataset on the server side only has this timestamp format and not a datetime column. How can I do this for different agg types such as sec, min, hr, and day and scale multipliers of those? (ie. 2 seconds, 7 hours, etc.) such that if I have data frequency in microseconds, and I covert it to every 3 hours over the timetamps provided above, I get something like this:

Output

I haven't tried anything yet because I don't know where to start with this as I haven't worked with UNIX time before.

Azure Data Structure I am trying to do agg/calc on:

Data

In order to aggregate the data in the table by a 3-hour interval and calculate the average of data1 and data2 for each interval, you can follow the below approach.

SQL code

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;

The first CTE (cte1) converts the Unix timestamps in the Mytable1 table to datetime values using the DATEADD function. The second CTE (cte2) calculates the 3-hour interval for each datetime value using the DATEPART function The final SELECT statement groups the data by date and interval and calculates the minimum timestamp and the average of data1 and data2 for each group.

db-fiddle