0

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;
Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
Andrew
  • 3,632
  • 24
  • 64
  • 113
  • So, what exactly is the question? – Mureinik Sep 22 '15 at 08:03
  • The question is i have select query in which i have to remove the mention condition where IS_DOWN=1 and put the condition which i have mentioned in my question. So the select query should be for example like this: SELECT COUNT(*) FROM TEMP_PROCESS_MONITOR WHERE (CURRENT_TIMESTMAP - ALERT_TIMESTAMP) > TIMEOUT – Andrew Sep 22 '15 at 08:05

1 Answers1

1

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)
Anton Zaviriukhin
  • 741
  • 4
  • 14
  • Can you please elaborate the answer how its returning the minutes from this two datetime fields ? I am getting one row returned now if i use your first condition and as menitoned in my table its returning the first row in which TIMEOUT has 120 and ALERT_TIMESTAMP has 17.06.15 16:29:33 value. – Andrew Sep 22 '15 at 08:11
  • What does *24*60 means ? – Andrew Sep 22 '15 at 08:12