Calculating Timestamp difference - doesnt work beyond 48 hours

Question

I am calculating difference between two timestamps, which somehow doesn't work when difference is more than 48 hours.

This is my code: select systimestamp, mystoredtimestamp, extract( hour from diff ) || ' hours ' || extract( minute from diff ) || ' minutes' from (select (systimestamp - mystoredtimestamp)) diff from dual)

The output is

1. systimestamp: 30-OCT-17 11.48.43.783572 AM +00:00,
2. mystoredtimestamp: 27-OCT-17 07.30.36.956687 AM,
3. difference: 4 hours 18 minutes

(somehow this markdown numbering is not being rendered, the above are the three different columns of the query)

mystoredtimestamp is in TIMESTAMP(6) WITH LOCAL TIME ZONE datatype. Is that whats causing the problem? If so, how do I resolve this now?

Answer 1

You need to extract days as well. It won't automatically convert to number of hours.

SELECT
  extract ( DAY FROM diff )
  || ' days '
  ||extract( hour FROM diff )
  || ' hours '
  || extract( minute FROM diff )
  || ' minutes'
FROM
  (
    SELECT
      systimestamp - CAST ( '27-OCT-17 07.30.36.956687 AM' AS TIMESTAMP(6)
      WITH LOCAL TIME ZONE ) diff
    FROM
      DUAL
  );

O/p

3 days 2 hours 26 minutes

If you want in hours, multiply by 24 and add days.

SELECT
  24 * extract ( DAY FROM diff )
   +
 extract( hour FROM diff )
  || ' hours '
  || extract( minute FROM diff )
  || ' minutes'
FROM
  (
    SELECT
      systimestamp - CAST ( '27-OCT-17 07.30.36.956687 AM' AS TIMESTAMP(6)
      WITH LOCAL TIME ZONE ) diff
    FROM
      DUAL
  );

O/p

74 hours 28 minutes