Post reply

Get last 30 minutes of activity from table

  • June 15, 2021 at 5:50 pm

    Go to Answer

    #3896609

    I need to pull the last 30 minutes of activity from a table that has a field defined and stored in Unix format, which is a bigint field with the number of milliseconds since 01/01/1970.

    The field is called t_stamp  I want to get the last 30 minutes of activity from that table.

    Thanks.

     

  • June 15, 2021 at 6:12 pm

    #3896611

    For millisecond precision it would be something like,

    SELECT DATEDIFF_BIG(ms, '19700101' ,DATEADD(minute, -30, getdate()))

  • June 15, 2021 at 6:21 pm

    #3896615

    This gets me the date from the table:

    DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') ..  I want to extract data 30 minutes prior from current datetime using the expression from t.stamp field  ... maybe I asked wrong..

    Thanks.

  • June 15, 2021 at 6:53 pm

    #3896632

    If you calculate the unix millisecond timestamp 30 minutes ago all you should need to do is look for time stamps greater than that.

  • June 15, 2021 at 6:56 pm

    #3896634

    WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE()) - (1000 * 60 * 30)

    Note that, for important performance reasons, you do not want to use any function at all on the column itself, only on the literal values / expressions.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 15, 2021 at 7:51 pm

    #3896663

    That looks correct to me, but I'm getting records with dates past even current date time. I'm checking on the process writing records to

    the table, but something is a miss ..

    Thanks.

  • June 15, 2021 at 7:52 pm

    #3896665

    I put no max time, so, yes, it would give you everything from then on.

    If you want to limit to the then-current time, do this:

    WHERE t_stamp >= DATEDIFF_BIG(MS, '19700101', GETDATE()) - (1000 * 60 * 30)

    AND t_stamp <= DATEDIFF_BIG(MS, '19700101', GETDATE())

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 15, 2021 at 7:59 pm

    #3896668

    I just pulled one of the latest records written to table and when I convert it it shows 2021-06-15 19:53:37.000

    THanks.

  • June 15, 2021 at 8:04 pm

    #3896669

    Hmm, looks like UTC time.

    Or something in the data -- i.e. it's not really ms since 1/1/70 -- or it's local time where the server is?!

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 15, 2021 at 8:10 pm

    #3896670

    I was thinking UTC  if I use your query that sets an EndTime data looks correct..

  • June 15, 2021 at 8:16 pm

    #3896672

    To get a UTC calc, just substitute GETUTCDATE() for GETDATE() in my code.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 15, 2021 at 8:28 pm

    #3896675

    I'm checking now the data

    Thanks.

  • June 15, 2021 at 11:01 pm

    #3896733

    Found that I had to use '1969-12-31 20:00:00' to get the correct datetime. So I'm looking for the most efficient way to pull the last 30 minutes of data from the table. This converts it to date\time I want to use DATEADD(s,t_stamp/1000,'1969-12-31 20:00:00'), but should I use t_stamp in it's field def as a BigInt as a  way to go back to pull the date.

    Thanks.

     

  • June 16, 2021 at 12:04 am

    #3896751

    Once this is converted to a time, this should be easy... get a MAX of the time, and then subtract 30 mins, so something like  [theTime]>=(SELECT DATEADD(minute,-30,MAX(Time))

  • June 16, 2021 at 12:09 am

    #3896754

    Not sure I'm following convert to time

    Thanks,

Viewing 15 posts - 1 through 15 (of 17 total)

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