• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

TSQL-How to get all rows within the last 15 minutes

I have a table that logs phone data. This table gets a row inserted every time a person who is logged into the phone system changes their state. So if I pick up the phone or hang up the phone, each one of those events is logged in the table. The table has a column named EventDateTime. This has the datetime of each event.
I would like to pull all rows that are only 15 minutes old. I am having trouble with this. I have attached the create table statement and the top 1000 rows from my table. Based on when this question gets viewed, the expert will have to adjust the time values. I really need to do this by minute, so if 1 hour gets the results, please use 60 minutes.

Thanks!

CREATE TABLE [dbo].[AgentStateDetail](
	[agentID] [int] NOT NULL,
	[eventDateTime] [datetime] NOT NULL,
	[gmtOffset] [smallint] NOT NULL,
	[eventType] [tinyint] NOT NULL,
	[reasonCode] [smallint] NOT NULL DEFAULT (0),
	[profileID] [int] NOT NULL,
 CONSTRAINT [PK_AgentStatusDetail] PRIMARY KEY NONCLUSTERED 
(

Open in new window

top1000rows-10.07.2008.txt
0
sqlagent007
Asked:
sqlagent007
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Try this:
(I made 35 minutes to go 15 minutes before 1:00PM)
SELECT
	[agentID],
	[eventDateTime],
	[gmtOffset],
	[eventType],
	[reasonCode],
	[profileID]
FROM [AgentStateDetail]
WHERE [eventDateTime] >= DateAdd(mi, -35, getdate())

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Just adjust 35 until you get records you want. ;)
0
 
BrandonGalderisiCommented:
Assuming your eventdatetime is written in local time, and not UTC:

select * from AgentStateDetail
where eventDatetime >= dateadd(n,-15,getdate())


If UTC

select * from AgentStateDetail
where eventDatetime >= dateadd(n,-15,getutcdate())
0
 
Kevin CrossChief Technology OfficerCommented:
Nice solution, Brandon.

>>Based on when this question gets viewed, the expert will have to adjust the time values.

I guess I read too much into that statement and tried to adjust.  Anyway, life goes on.  I will live to help another day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now