How to find out differences between two timestamps and extract only hours and minutes

Question

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)

Please describe what does "*I am not getting the expected result*" mean. Timestamp with time zone is not the same as pure timestamp, which you are trying to get via `to_timestamp`. Try to use `to_timestamp_tz('2022-09-29 02:27:48.696000000 PM +05:30', 'yyyy-mm-dd hh12:mi:ss.ff PM TZH:TZM')` — astentx, Sep 28 '22 at 09:14
If the difference could be more than 24 hours, and/or it could be negative, [then you might have to do a bit more work](https://dbfiddle.uk/u0ULdrjz). — Alex Poole, Sep 28 '22 at 10:01

score 0 · Accepted Answer · answered Sep 28 '22 at 09:16

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

fiddle

How to find out differences between two timestamps and extract only hours and minutes

1 Answers1