How to fetch records having difference of 5 minutes or less | Oracle

Question

I am currently stuck with this approch not getting how do i need to minus with current timestamp and find all records which as a difference of 5 minutes or less

My table

create table demo ( id number, modified_date timestamp(6) );

insert into demo values(1,to_timestamp('10-MAY-19 07.14.15.066000000 AM','DD-MON-RR HH.MI.SSXFF AM'));   
insert into demo values(5,to_timestamp('10-MAY-19 07.16.11.064000000 AM','DD-MON-RR HH.MI.SSXFF AM'));    
insert into demo values(3,to_timestamp('10-MAY-19 07.15.13.062000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
insert into demo values(12,to_timestamp('10-MAY-19 07.18.10.056000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
insert into demo values(14,to_timestamp('10-MAY-19 07.20.09.046000000 AM','DD-MON-RR HH.MI.SSXFF AM'));

Data in table

id  modified_date 
1   10-MAY-19 07.14.15.066000000 AM
5   10-MAY-19 07.16.11.064000000 AM
3   10-MAY-19 07.15.13.062000000 AM
12  10-MAY-19 07.18.10.056000000 AM
14  10-MAY-19 07.20.09.046000000 AM

My query does not give in minutes

select modified_date - current_timestamp as dt_minutes from demo;

Expected : Need record as a difference of 5 minutes or less with current_timestamp

id  modified_date                    date_differenence    
1   10-MAY-19 07.14.15.066000000 AM  modified_date - current_timestamp ( in minutes )
5   10-MAY-19 07.16.11.064000000 AM  modified_date - current_timestamp ( in minutes )
3   10-MAY-19 07.15.13.062000000 AM  modified_date - current_timestamp ( in minutes )
12  10-MAY-19 07.18.10.056000000 AM  modified_date - current_timestamp ( in minutes )
14  10-MAY-19 07.20.09.046000000 AM  modified_date - current_timestamp ( in minutes )

Note:

NLS_LANGUAGE = 'AMERICAN'
NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM TZR'
NLS_CALENDAR ='GREGORIAN'

Answer 1

Use an 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;

Use ABS(EXTRACT(MINUTE FROM current_date - modified_date)) if you are not worried about the sign.

Which, for the sample data:

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;

Outputs:

ID	MODIFIED_DATE	CURRENT_DATE	INTERVAL_DIFFERENCE	MINUTES_DIFFERENCE
3	2024-07-07 15:42:27.262701	2024-07-07 15:37:27	+000000000 00:04:59.975026	4
4	2024-07-07 15:42:26.262701	2024-07-07 15:37:27	+000000000 00:04:58.975026	4
5	2024-07-07 15:37:27.262701	2024-07-07 15:37:27	-000000000 00:00:00.024974	0
6	2024-07-07 15:32:28.262701	2024-07-07 15:37:27	-000000000 00:04:59.024974	-4

fiddle

Answer 2

I tried it in this way and it worked. In case any issue in mu query. Kindly help me with correcting it along with explanation.

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;