SQL Select Statement to Retrieve a Frequency of Timestamps-CodePudding

I need to select the first DateTime, Value, and ID for every 5 minutes. The frequency of the pi data is highly granular. I am using SQL Server. Any help would be appreciated.

ID  DateTime    Value
-----------------------------------
ID1 2022-05-03 08:50:00.0370000 100
ID1 2022-05-03 08:50:00.1370000 105
ID1 2022-05-03 08:55:05.0371200 100
ID1 2022-05-03 08:55:05.3210000 115
ID1 2022-05-03 08:55:05.5320000 130
ID1 2022-05-03 08:56:06.4220000 110
ID1 2022-05-03 08:58:08.0312300 105
ID1 2022-05-03 09:00:00.0424400 100

Desired Dataframe:

ID   DateTime              Value
--------------------------------
ID1  2022-05-03 08:50:00    100
ID1  2022-05-03 08:55:05    100
ID1  2022-05-03 09:00:00    100

My attempt so far:

WITH added_row_number AS 
(
    SELECT
        [ID],
        FORMAT([DateTime], 'dd-MM.yyyy HH:mm') AS DateTime,
        [Value],
        ROW_NUMBER() OVER(PARTITION BY [DateTime] ORDER BY [DateTime] ASC) AS row_number
    FROM 
        [dbo].[table]
)
SELECT
    *
FROM 
    added_row_number
WHERE 
    row_number = 1;

CodePudding user response：

I think what's needed here is a 5 minute time interval by which you can partition your row_number():

I just poached this:

dateadd(minute, datediff(minute, '1900-01-01', dateadd(second, 150, @date))/5*5, 0)

From here but I'm sure there are quite a few other ways to round or floor a datetime to a 5 minute interval that may be more suitable.

WITH added_row_number AS 
(
    SELECT
        [ID],
        FORMAT([DateTime], 'dd-MM.yyyy HH:mm') AS DateTime,
        [Value],
        ROW_NUMBER() OVER(PARTITION BY dateadd(minute, datediff(minute, '1900-01-01', dateadd(second, 150, [DateTime]))/5*5, 0) ORDER BY [DateTime] ASC) AS row_number
    FROM 
        [dbo].[table]
)
SELECT
    *
FROM 
    added_row_number
WHERE 
    row_number = 1;

CodePudding user response：

I hope this is what you looking for.

 ;WITH OrderedSet AS 
(
    SELECT ID,
          [Value],
          [DateTime], 
          ROW_NUMBER() OVER(PARTITION BY (DATEDIFF(MINUTE, '1900-01-01T00:00:00', [DateTime])/5), FORMAT(T.[DateTime], 'yyyyddMMHH') ORDER BY T.[DateTime]) rn
    FROM [table] T
)
SELECT * FROM OrderedSet
WHERE rn= 1

Added this on Fiddle