Add minutes to an Oracle date-CodePudding

I am trying to add minutes to my sql but no error and no data.

This is my sql

select distinct mo.reference_no payment_id, mo.dcn message_id,
mo.amount, mo.ccy, decode (mo.msg_status, 'R', 'Repair', 'P',
'Processed','N','Ungenerated','G','Generated',mo.msg_status)
message_status, to_char 
(MO.INSERT_TIME,'DD-MM-YYYY HH24:MI:SS')
paym_date, mo.branch_date, mo.maker_id 
from table1 mo
left join table2 mi 
on mo.reference_no = mi.generated_ref_no 
where mo.swift_msg_type = 103
and mo.ccy = 'XXX' and mo.branch_date = trunc(sysdate)
and mo.msg_status in('R','N','G','P')
order by PAYM_DATE desc;

And I want to add 5 minutes to (MO.INSERT_TIME,'DD-MM-YYYY HH24:MI:SS')

So I want my sql show me after 5 minutes for this column (MO.INSERT_TIME,'DD-MM-YYYY HH24:MI:SS')

I wrote sql like that

select distinct mo.reference_no payment_id, mo.dcn message_id,
mo.amount, mo.ccy, decode (mo.msg_status, 'R', 'Repair', 'P',
'Processed','N','Ungenerated','G','Generated',mo.msg_status)
message_status, to_char 
(MO.INSERT_TIME,'DD-MM-YYYY HH24:MI:SS')
paym_date, mo.branch_date, mo.maker_id 
from table1 mo
left join table2 mi 
on mo.reference_no = mi.generated_ref_no 
where mo.swift_msg_type = 103
and mo.ccy = 'XXX' and mo.branch_date = trunc(sysdate)
and MO.INSERT_TIME = to_date (sysdate,'DD-MM-YYYY HH24:MI:SS')   INTERVAL '5' MINUTE
and mo.msg_status in('R','N','G','P')
order by PAYM_DATE desc;

And after 5 minutes I am selecting this sql and their is no data. Can you explain why?

CodePudding user response：

This is what you used:

and MO.INSERT_TIME = to_date (sysdate,'DD-MM-YYYY HH24:MI:SS')   INTERVAL '5' MINUTE

It is wrong because you're applying TO_DATE function to SYSDATE which already returns DATE datatype, so there's no point in doing it.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate,
  2         sysdate   interval '5' minute
  3  from dual;

SYSDATE             SYSDATE INTERVAL'5'
------------------- -------------------
10.01.2023 09:49:05 10.01.2023 09:54:05

SQL>

It is probably not very likely that MO.INSERT_TIME will exactly be the same as SYSDATE (which is right now) 5 minutes, which is in the future.

Perhaps you'd rather set that condition to e.g. rows inserted during last 5 minutes (which makes more sense to me):

and MO.INSERT_TIME >= sysdate - INTERVAL '5' MINUTE