300 300 - 2 months ago 5
SQL Question

How to select rows with highest time stamp in 30 minute window?

I have SQL Server Table named table1 which has one timestamp column column_ts and some more columns say column1, column2, column3

So table looks like:

column_ts column1 column2 column3
2016-09-30 00:04:00.000 number1 string1 integer1
2016-09-30 00:24:00.000 number2 string2 integer2
2016-09-30 00:29:00.000 number3 string3 integer3
2016-09-30 00:44:00.000 number4 string4 integer4
2016-09-30 00:48:00.000 number5 string5 integer5
2016-09-30 01:04:00.000 number6 string6 integer6
2016-09-30 01:24:00.000 number7 string7 integer7
2016-09-30 01:54:00.000 number8 string8 integer8
2016-09-30 01:59:00.000 number9 string9 integer9


First, I will select records
where column_ts >= 2016-09-30 00:00:00.000
. Then out of these, I want to select only one row that has highest time stamp from each 30 minute window of column_ts.

So for the given data, the query should select only following rows:

column_ts column1 column2 column3
2016-09-18 00:29:00.000 number3 string3 integer3
2016-09-18 00:48:00.000 number5 string5 integer5
2016-09-18 01:24:00.000 number7 string7 integer7
2016-09-18 01:59:00.000 number9 string9 integer9


In a way, I want to make 30 minute windows of column_ts like

1) 2016-09-30 00:00:00.000 - 2016-09-30 00:30:00.000

2) 2016-09-30 00:30:00.000 - 2016-09-30 01:00:00.000

3) 2016-09-30 01:00:00.000 - 2016-09-30 01:30:00.000

4) 2016-09-30 01:30:00.000 - 2016-09-30 02:00:00.000

And finally want to select one row which has highest value for column_ts from each of these 30 minute windows.

I am not able to figure out how I can generate the 30 minute window from which I can select
MAX(column_ts)
. Please suggest how I can do this.

Answer

You can take the date difference in minutes from an epoch and then divide that by 30 to group by 30 minute intervals.

This query will give each 30 minute slot along with the max column_ts for that slot:

select dateadd(minute, datediff(minute, '1970-1-1',column_ts)/30*30,'1970-1-1') as timegroup,
       MAX(column_ts) as max_time
from table1 where column_ts >= '2016-09-30 00:00:00.000'
group by datediff(minute, '1970-1-1', column_ts) / 30

The above produces:

timegroup                   max_time
2016-09-30 00:00:00.000     2016-09-30 00:29:00.000
2016-09-30 00:30:00.000     2016-09-30 00:48:00.000
2016-09-30 01:00:00.000     2016-09-30 01:24:00.000
2016-09-30 01:30:00.000     2016-09-30 01:59:00.000

Once you have that, you can use it in a sub-query to get the results you are after:

select groups.timegroup, t.column_ts, t.column1, t.column2, t.column3 
from (
    select dateadd(minute, datediff(minute, '1970-1-1',column_ts)/30*30,'1970-1-1') as timegroup,MAX(column_ts) as max_time
    from table1 where column_ts >= '2016-09-30 00:00:00.000'
    group by datediff(minute, '1970-1-1', column_ts) / 30
) as groups
inner join table1 t on t.column_ts = groups.max_time

Which produces

timegroup                   column_ts                   column1   column2   column3
2016-09-30 00:00:00.000     2016-09-30 00:29:00.000     number3   string3   integer3
2016-09-30 00:30:00.000     2016-09-30 00:48:00.000     number5   string5   integer5
2016-09-30 01:00:00.000     2016-09-30 01:24:00.000     number7   string7   integer7
2016-09-30 01:30:00.000     2016-09-30 01:59:00.000     number9   string9   integer9