Get last 30 minutes of activity from table

Question

Post reply

Get last 30 minutes of activity from table

Bruin

SSCrazy

Points: 2058
More actions
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.

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

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

For millisecond precision it would be something like,

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

Bruin SSCrazy Points: 2058 More actions · Answer 2

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.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 5

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.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 7

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.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 9

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

ScottPletcher SSC Guru Points: 101105 More actions · Answer 10

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 11

Bruin

SSCrazy

Points: 2058

June 15, 2021 at 8:28 pm

#3896675

I'm checking now the data

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 12

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.

pietlinden SSC Guru Points: 63503 More actions · Answer 13

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))

Bruin SSCrazy Points: 2058 More actions · Answer 14

Bruin

SSCrazy

Points: 2058

June 16, 2021 at 12:09 am

#3896754

Not sure I'm following convert to time

Thanks,