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 ( in minutes ) as dt 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 )
quora question is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.