Как получить записи с разницей в 5 минут или меньше | Оракул

Используйте INTERVAL DAY TO SECOND:

SELECT id,
       modified_date,
       current_date,
       modified_date - current_timestamp AS interval_difference,
       EXTRACT(MINUTE FROM modified_date - current_timestamp) AS minutes_difference
FROM   demo
WHERE  modified_date BETWEEN CURRENT_TIMESTAMP - INTERVAL '5' MINUTE
                         AND CURRENT_TIMESTAMP + INTERVAL '5' MINUTE;

Используйте ABS(EXTRACT(MINUTE FROM current_date - modified_date)), если знак вас не беспокоит.

Что для примера данных:

CREATE TABLE demo (
  id NUMBER,
  modified_date TIMESTAMP(6)
);

INSERT INTO demo (id, modified_date)
  SELECT 1, CURRENT_TIMESTAMP + INTERVAL '10' MINUTE FROM DUAL UNION ALL
  SELECT 2, CURRENT_TIMESTAMP + INTERVAL '5:01' MINUTE TO SECOND FROM DUAL UNION ALL
  SELECT 3, CURRENT_TIMESTAMP + INTERVAL '5:00' MINUTE TO SECOND FROM DUAL UNION ALL
  SELECT 4, CURRENT_TIMESTAMP + INTERVAL '4:59' MINUTE TO SECOND FROM DUAL UNION ALL
  SELECT 5, CURRENT_TIMESTAMP + INTERVAL '0' MINUTE FROM DUAL UNION ALL
  SELECT 6, CURRENT_TIMESTAMP - INTERVAL '4:59' MINUTE TO SECOND FROM DUAL UNION ALL
  SELECT 7, CURRENT_TIMESTAMP - INTERVAL '5:01' MINUTE TO SECOND FROM DUAL UNION ALL
  SELECT 8, CURRENT_TIMESTAMP - INTERVAL '5' YEAR FROM DUAL;

Выходы:

рабочий пример

Я попробовал это таким образом, и это сработало. В случае возникновения каких-либо проблем в запросе mu. Пожалуйста, помогите мне исправить это вместе с объяснением.

SELECT
    id,
    modified_date,
    EXTRACT(MINUTE FROM CAST(current_timestamp AS TIMESTAMP))                                                         AS cur_minutes,
    EXTRACT(MINUTE FROM CAST(modified_date AS TIMESTAMP))                                                             AS mod_minutes,
    EXTRACT(MINUTE FROM CAST(current_timestamp AS TIMESTAMP)) - EXTRACT(MINUTE FROM CAST(modified_date AS TIMESTAMP)) AS min_diff
FROM
    demo
WHERE
    modified_date > current_timestamp - INTERVAL '5' MINUTE;