How can query last 5 minutes of records?

Question

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

Answer 1

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)

Answer 2

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

Answer 3

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.