CODEWITHSUNDEEP
sqloracle-databasedatetimeminute
Marcus
Marcus

Reputation: 3869

Subtract two date field values and get the minutes

I have below TEMP_PROCESS_MONITOR table which has ALERT_TIMESTAMP column which has date data type.The TIMEOUT column has Number data type and it consist of Minutes values. I want to subtract the Current date with the ALERT_TIMESTAMP field and get the minutes and then compare this minutes with TIMEOUT field in the below select query. TEMP_PROCESS_MONITOR table: enter image description here

And the condition should be for example like this:

(CURRENT_TIMESTMAP - ALERT_TIMESTAMP) > TIMEOUT

So i have to remove the Where condition from the below query and put the condition which i have mentioned above:

SELECT COUNT(*) FROM TEMP_PROCESS_MONITOR WHERE IS_DOWN = 1;

Upvotes: 0

Views: 1264

Answers (1)

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

To get current date with seconds in date format you may use SYSDATE SQL function.
Date subtraction gives number of days (with fraction) as a result.
So you need either translate this number to minutes:

(SYSDATE - ALERT_TIMESTAMP)*24*60 > TIMEOUT

or translate timeout to days:

(SYSDATE - ALERT_TIMESTAMP) > TIMEOUT/(24*60)

Upvotes: 1

Related Questions