CODEWITHSUNDEEP
sqlsql-server-2008
Julia
Julia

Reputation: 83

How to get record for every 2 minutes in SQL

I have a data table that has entries for every one minute, it's bulky dataset. So I need to get every 2 minutes data retrieving 10 minutes dataset from last valid record. All these data is used in the graph drawing, so trying to limit the number of records displayed in the chart. eg for a sample looks like this:

DateTime
2016-01-01  08:22:00
2016-01-01  08:21:00
2016-01-01  08:20:00
2016-01-01  08:19:00
2016-01-01  08:18:00
2016-01-01  08:17:00
2016-01-01  08:16:00
2016-01-01  08:15:00
2016-01-01  08:14:00
2016-01-01  08:13:00
2016-01-01  08:12:00
2016-01-01  08:11:00
2016-01-01  08:10:00

Expected records result:

2016-01-01  08:21:00
2016-01-01  08:19:00
2016-01-01  08:17:00
2016-01-01  08:15:00
2016-01-01  08:13:00

How can I do it in SQL query?

Upvotes: 1

Views: 3144

Answers (2)

EzLo
EzLo

Reputation: 14189

For retrieving the values you can use the following:

SELECT
    *
FROM
    YourTable AS T
WHERE
    T.DateTimeColumn >= DATEADD(MINUTE, -10, GETDATE())

And for your periodical task you can create a SQL Server Agent job that runs the previous query every 2 minutes.

Upvotes: 1

SChowdhury
SChowdhury

Reputation: 163

If you are using SQL Server :

  1. You can schedule a job in SQL Server Agent

  2. Write a Loop in SQL and run it. You may alter @end_time calculation for increasing time limit from 10 to something bigger.

    DECLARE @interval int = 2, 
                @start_time datetime , 
                @end_time datetime   

 set @start_time = GETDATE();
 set @end_time = DATEADD(M, 10, @start_time)

 while (@start_time < @end_time)
    BEGIN
    select * 
    from [TABLENAME ]
    where [col] between @start_time and DATEADD(M, @interval, @start_time)

        set @start_time = DATEADD(M, @interval, @start_time)     END

Upvotes: 1

Related Questions