Intervals between samples

Transact-SQL
1

Hey,
I've got table with 2 fields: TIME_STAMP (datetime) and SAMPLE_VALUE (int).
I've been asked to query the table and extract rows with interval time between them. For example:
10:01:50
10:02:20:
10:02:40
10:02:51
10:03:20
10:03:52
10:05:00
will return:
10:01:50
10:02:51
10:03:52
10:05:00

2

from the pattern you are providing should it not be
10:01:50
10:02:51
10:03:52
10:04:53
10:05:54

instead of
10:01:50
10:02:51
10:03:52
10:05:00

3

No,
The samples are given and stored in table. There is no constant interval between samples.
Once you query the table, you don't need to extract all rows but the first row and then the next row which its timestamp is greater than 1 minute from the previous one.
I read again my question and it seems I missed one thing: the intervals between any two rows should be at least 1 minute.

4

try this sample out and notice the result. see if you can extract something out of this sample. if you could provide real world data would be easier to solve

create table #orenshto(timeinterval time not null)

insert into #orenshto
select '10:01:50' union
select '10:02:20' union
select '10:02:40' union
select '10:02:51' union
select '10:03:20' union
select '10:03:52' union
select '10:05:00' 

--create table #miso(timeinterval time not null, hr int, mnt int, sec int);

;WITH bento(timeinterval)
  AS
  (
    SELECT REPLACE( timeinterval,'.',':')
    FROM #orenshto
  ),
  shashimi (timeinterval, xmlname)
  AS
  (
    SELECT timeinterval,
    CONVERT(XML,'<Names><name>'  
    + REPLACE( timeinterval,':', '</name><name>') + '</name></Names>') AS xmlname
      FROM bento
  ),
  ricebowl (timeinterval, hr, mnt, sec)
  AS
  (
 SELECT timeinterval,      
        xmlname.value('/Names[1]/name[1]','varchar(100)') AS hr,    
        xmlname.value('/Names[1]/name[2]','varchar(100)') AS mnt,
		xmlname.value('/Names[1]/name[3]','varchar(100)') AS sec

 FROM shashimi
  )
select hr, mnt, max(sec)
from ricebowl
 group by hr, mnt

drop table #orenshto
5

Try this

with myCte
as
(
select t.timeinterval, lead(t.timeinterval) over (partition by datepart(minute,t.timeinterval) order by t.timeinterval) as flag from #t as t
)
select timeinterval from myCte
where flag is null
1 Like