This is used in a database package as a delay to pass data. The delay is 2 minutes so I need to only retrieve records if they've been in the database for 2 minutes or more.

This is what I have:

((SYSDATE - trunc(last_updated))*24*60) > l_delay_mins;


l_delay_mins = 2 minutes in this case.

However, the trunc cuts off the time and defaults to midnight, when the conversion is done it gives me the fraction of the day and then multiplies 24*60 which is always larger than 2, so the records aren't being delayed and are sending as soon as they arrive.

2 Answers
2

A minor amendment to the above answer

Use INTERVAL:

INTERVAL

where last_updated <= systimestamp - (l_delay_mins * interval '1' minute);


Use INTERVAL:

INTERVAL

where last_updated < systimestamp - (l_delay_mins * interval '1' minute);


Here is the same thing without INTERVAL but with a minute being a fraction of the day (kind of what you tried):

INTERVAL

where last_updated < systimestamp - (l_delay_mins / 24 / 60)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.