Q:

What is the equivalent function I should use for my query in OracleDB?

Here is the where clause I current using in Mysql, I need to write this into Oracle with same logic.
The consprtydate column is in Timestamp(6) type.
WHERE ( ( rs.liferemaining <= 99999 ) 
   OR ( Abs(( Timestampdiff(hour, pk.consprtydate, rs.consprtydate) / 24 )) >= 1 ) )

Can any of your genius can help will be much appreciate.

A:

Function timestampdiff, according to documentation, in your case returns number of hours between pk.consprtydate and rs.consprtydate.
Number of hours divided by 24 returns number of days between them.
So, why didn't you timestampdiff(day, ...) instead?

In Oracle, when you subtract two DATE datatype values, you get number of days between them. Therefore, that would be
or abs(pk.consprtydate - rs.consprtydate) >= 1

