CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mahiz
Mahiz

Reputation: 295

Query to fetch records which are updated within last 60 mintues

My Sample Table looks like this ..

Primary_Key  || Start_Time || End_Time || Value

1 || 24-FEB-13 18:00:00 || 24-FEB-13 19:00:00 || 6

The data types of these columns are 

Primary_Key --   Int  
Start_Time  ---  TimeStamp  
End_Time    ---  TimeStamp  
Value       ---  Int

Now , i want to fetch all the records in the table which have the End_Time value within the 60 mins of the current time ?

Upvotes: 1

Views: 1380

Answers (4)

Art
Art

Reputation: 5792

You may try this - select your data between your_start_date and your_start_date + INTERVAL '60' MINUTE:

SELECT SYSDATE start_dt, (SYSDATE + INTERVAL '60' MINUTE) end_dt 
  FROM dual
/

SELECT * FROM
(
SELECT  to_date('24-FEB-13 18:00:00', 'DD-MON-YYYY HH24:MI:SS') your_start_date
       ,(to_date('24-FEB-13 18:00:00', 'DD-MON-YYYY HH24:MI:SS') + INTERVAL '60' MINUTE) your_end_dt 
 FROM dual
)
/

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

Try this...

SELECT * FROM yourTable
WHERE end_time >= CURRENT_TIMESTAMP - NUMToDSInterval(1, 'HOUR')

That should work with DATE and TIMESTAMP and TIMESTAMP WITH TIME ZONE values.

Upvotes: 1

rs.
rs.

Reputation: 27467

Try this

select * from yourtable
WHERE (sysdate - End_Time) * 1440 between 0 and 60

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

This should work:

select *
from sample s
where CURRENT_TIMESTAMP - end_time <= 1/24.0 and CURRENT_TIMESTAMP >= end_time

Upvotes: 0

Related Questions