How to find out the difference between two timestamps and get only hours and minutes from the difference? I tried using the extract function or doing straight subtraction but I am not getting the expected result.
select (to_timestamp('29-09-22 2:27:48.696000000 PM +05:30') - systimestamp)col from dual;
DB Version: Oracle SQL Developer (18c)
>Solution :
Use EXTRACT:
SELECT EXTRACT(DAY FROM diff)*24 + EXTRACT(HOUR FROM diff) AS hours,
EXTRACT(MINUTE FROM diff) AS minutes
FROM (
select TIMESTAMP '2022-09-29 14:27:48.696000000 +05:30' - systimestamp AS diff
from dual
);
Which outputs:
| HOURS | MINUTES |
|---|---|
| 23 | 41 |