Search code examples
sqlderby

How to write a query to fetch data from DB on the basis of timestamp

I have a task to do in which i have to write a query which can fetch the data on the basis of timestamp

  1. i have a table in my db having two columns one is event and other one is Time
  2. what i am trying to do is to write a query which can give me data entered in last 1 minutes
  3. I have written a query which is giving me correct result but in sql 5.1
  4. i am using Derby data base and facing issue to get the output

This is my database

this is the error i am getting from running my query in derby

error Syntax error: Encountered "5"

my query is SELECT * FROM test WHERE Time >= CURRENT_TIMESTAMP - INTERVAL 5 minute AND Time <= CURRENT_TIMESTAMP

Solution

  • I'm not sure about the interval syntax, but you can try TIMESTAMPADD():

    SELECT *
FROM test
WHERE Time >= {fn TIMESTAMPADD(SQL_TSI_MINUTE, -5, CURRENT_TIMESTAMP) }
AND Time <= CURRENT_TIMESTAMP