CODEWITHSUNDEEP
sqloracle-databaseplsql
Harish Puli
Harish Puli

Reputation: 49

Using MAX function for time in where condtion

Hi I have table where new rows get inserted every 5 seconds. Below is the query i am using to get the latest record of each Index ID with max index_end_time.

enter image description here

Now I need to check if there are any records whose MAX(INDEX_END_TIME) is elapsed more than 5 minutes than the current system time stamp.

some thing like below

SELECT  count(INDEX_ID) 
  FROM PS8BPMDB.BPM_TASK_INDEX_JOB 
  GROUP BY INDEX_ID 
  HAVING   MAX(INDEX_END_TIME) > SYS_EXTRACT_UTC(SYSTIMESTAMP)UTC_SYS - 5 minutes;

Upvotes: 0

Views: 52

Answers (2)

Harish Puli
Harish Puli

Reputation: 49

Another query.. I wanted to check if latest record's index_end_time elapsed more than 5 minutes..

select count(*) from (SELECT MAX(INDEX_END_TIME) as max_end_time,  INDEX_ID as idx_id FROM PS8BPMDB.BPM_TASK_INDEX_JOB GROUP BY INDEX_ID)
where max_end_time < SYS_EXTRACT_UTC(SYSTIMESTAMP)- interval '5' minute;

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18695

To do calculations on a column of data type TIMESTAMP you need to use the INTERVAL datatype.

SELECT count(INDEX_ID) 
  FROM PS8BPMDB.BPM_TASK_INDEX_JOB 
  GROUP BY INDEX_ID 
HAVING MAX(INDEX_END_TIME) > systimestamp - interval '5' minute;

Upvotes: 2

Related Questions