postgresql: How to use variables(some column value of a row) in where clause

I have a table with columns id,time,value

STEP1: get the time value from the recent row

i.e SELECT time FROM tablename ORDER BY id desc LIMIT 1

STEP2: get all the rows who are within last 5 minutes from this time.

How can i do this

Answer

One method is a subquery:

SELECT t.*
FROM tablename t
WHERE t.time > (SELECT t2.time - INTERVAL '5 minute'
                FROM tablename t2
                ORDER BY id desc
                LIMIT 1
               );

If this is really not the most recent time and you want anything within five minutes, then moving the calculation to the FROM clause is simpler:

SELECT t.*
FROM tablename t CROSS JOIN
     (SELECT t2.time - INTERVAL '5 minute'
      FROM tablename t2
      ORDER BY id desc
      LIMIT 1
     ) last_row
WHERE t.time > last_row.time - INTERVAL '5 minute' AND
      t.time <= last_row.time;

If you want the most recent time and anything within five minutes of that, you can use window functions as well:

SELECT t.*
FROM (SELECT t.*,
             MAX(t.time) OVER () as max_time
      FROM t
     ) t
WHERE t.time > max_time - INTERVAL '5 minute';