CODEWITHSUNDEEP
sqlplsqloracle10gdate-arithmetic
zeesu
zeesu

Reputation: 57

how to pickup record in oracle sql 10g when the time difference is 5 minutes

I have table which have column date_col . There is job which fetch data from this table every 5 minute

I want to pick up record which have changed in last 5 minute

Similarity for another table it is 1 hour

How can I do that?

Upvotes: 0

Views: 58

Answers (3)

APC
APC

Reputation: 146199

We can do basic arithmetic with Oracle dates. So to find datetimes of five minutes' ago we can do this: where date_col >= sysdate - (5/(24*60)).

A slightly more expressive approach uses the interval datatype. For ranges of days to seconds there is the TO_DSINTERVAL() function: where date_col >= sysdate - to_ds_interval('0 0:5:0'). An hour interval would be where date_col >= sysdate - to_ds_interval('0 1:0:0').

Your approach proposes using sysdate. This is fine as a first step but perhaps not resilient enough for a real application. What happens if your job runs for more than five minutes? Or dies and has to be restarted? A more robust solution would be to log job start times and pick up records which have been touched since the previous job ran.

Upvotes: 0

simplify_life
simplify_life

Reputation: 405

Assuming your date_col hold the audit of last changed records

select * from table1 where date_col between  sysdate-(5/(24*60)) and sysdate

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

it is very simple in oracle with the help of function sysdate. this function returns the current date value from DB server.

so you can use something like - 

 select * 
  from <your-table> 
  where date_col > sysdate - ((5/24)/60)

similarly for one hour -- sysdate - (1/24)

hope this helps you in your task.

Upvotes: 1

Related Questions