Post reply

how to get last 15min of data from online table

  • August 13, 2009 at 10:24 am

    #138136

    how to get last 15min of data from online table, I do have a datatime field in my table

  • August 13, 2009 at 10:33 am

    #1038830

    you'll want to compare that column against the current datetime, which you get from the built in GETDATE() function.

    you also want to use the DATEDIFF function to return minutes():

    ie:

    SELECT *

    FROM YOURTABLE

    WHERE DATEDIFF(minute,YourDateColumn,GETDATE()) > 15

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 13, 2009 at 10:39 am

    #1038837

    Thanks

  • August 13, 2009 at 10:40 am

    #1038838

    Lowell (8/13/2009)

    you'll want to compare that column against the current datetime, which you get from the built in GETDATE() function.

    you also want to use the DATEDIFF function to return minutes():

    ie:

    SELECT *

    FROM YOURTABLE

    WHERE DATEDIFF(minute,YourDateColumn,GETDATE()) > 15

    So very close and yet...:)

    Try with

    WHERE DATEDIFF(minute,YourDateColumn,GETDATE()) <= 15

    Otherwise you end up with everything EXCEPT the last 15 minutes.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • August 13, 2009 at 10:44 am

    #1038842

    one more thing - if your online table is big, but indexed based on that date - you might have a better shot at a good index usage with:

    declare @g datetime

    set @g=dateadd(minute,-15,getdate());

    SELECT *

    FROM YOURTABLE

    WHERE YourDateColumn> @g

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • August 13, 2009 at 10:47 am

    #1038845

    Thanks to all, I got it

  • August 13, 2009 at 11:30 am

    #1038871

    doh thanks Matt; I did flub that up to get more than 15 instead of less than 15.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply