CODEWITHSUNDEEP
oracle-databasedate-arithmetic
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

ORACLE query check record exist or not - hourly

Example I have table with data below:

FORMID | QUANTITY | DATEADDED
1      | 150      | 4/12/2017 2:33:27 PM
2      | 100      | 4/12/2017 3:15:10 PM

I want the query to check the data hourly exist or not based on current time.

Example

Current time: 18:00

So when query check the data, it should no data because We see the last data is at 3:15:10 PM and current time is 18:00(more than 2 hours ago).

I don't have idea how to do it,

SELECT * FROM TBQUANTITY WHERE...

Upvotes: 0

Views: 90

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21075

For a simple ad Hoc query to see if there are some records use SYSDATE as proposed in the other answer.

If your target is to get all records and process each record only once, more care should be taken.

You can hourly schedule a query selecting the data from tha last hour such as

 select * from T where DATEADDED >  trunc(sysdate,'HH') -1/24 
     and DATEADDED <= trunc(sysdate,'HH')

Note that this query select the data from the last full hour (trunc on HH) e.g. from 12-04-2017 17:00:00 to 12-04-2017 18:00:00 and you use a half-open interval not BETWEEN which would select records with a timestamp of a whole hour twice.

Upvotes: 0

MT0
MT0

Reputation: 167981

This will get all rows where the DATEADDED column is between now (using server time) and one hour ago.

SELECT *
FROM   TBQUANTITY
WHERE  dateadded BETWEEN SYSDATE - INTERVAL '1' HOUR AND SYSDATE

Upvotes: 1

Related Questions