CODEWITHSUNDEEP
sqloracle-database
user3559774
user3559774

Reputation: 115

How can query last 5 minutes of records?

How I can to see last 5 mins record before the current time through sql query how i can do this. The format of time stamp is 

03/25/2014 14:00:00

I used this query for the same 

SELECT Time stamp FROM TABLE 
WHERE S >1 AND SUBSTRING((Time stamp,15,2)-5)

is this fine of any other way to do the same

Upvotes: 11

Views: 84012

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If your timestamp is a date column, you can simply do:

select t.*
from table t
where t.timestamp >= sysdate - 5/(24*60)

Things are a bit more interesting if timestamp is a character column. Then you need to translate it to a date/time:

select t.*
from table t
where to_date(t.timestamp, 'MM/DD/YYYY HH24:MI:SS') >= sysdate - 5/(24*60)

Upvotes: 12

user330315
user330315

Reputation: 

select *
from the_table
where timestamp_column <= timestamp '2014-03-25 14:00:00' - interval '5' minute;

This assumes that timestamp_column is defined with the data type timestamp.

If it isn't you should stop now and re-define your table to use the correct data type.

The expression timestamp '2014-03-25 14:00:00' is a (ANSI SQL) timestamp literal.

It's equivalent to to_timestamp('2014-03-25 14:00:00', 'yyyy-mm-dd hh24:mi:ss') but I prefer the ANSI literal because it's less typing and works across multiple DBMS.

Upvotes: 8

xQbert
xQbert

Reputation: 35323

Here's an example of how to get 5 minutes ago in oracle. subtracting from a timestamp in increments of 1 where 1 is a day. so 5 minutes would be 5/(24hours*60min) of a day.

SELECT sysdate, sysdate-(5/(24*60)) as min_Ago5 from dual

Upvotes: 2

juergen d
juergen d

Reputation: 204746

If you are using MySQL and your timestamp column is of data type datetime you can do

SELECT Timestamp 
FROM your_table
WHERE Timestamp >= now() - interval 5 minute

Upvotes: 13

Related Questions