Subtract ISO 8601 timestamps in Oracle

Question

I want to subtract two timestamps in ISO 8601 format (2021-08-24T12:59:35Z - 2021-08-24T12:59:05Z) Think the easiest way is convert this timestamp to epoch and then subtract. Just can't get the syntax right for the conversion. How do I convert 2021-08-24T12:59:05Z to this : 1629809975 ?

Answer 1

Like the comment says, a lot depends on how you want the result formatted.

If you are ok with an INTERVAL, then the easiest thing to do is use TO_UTC_TIMESTAMP_TZ which actually takes an ISO 8601 formatted string as a parameter:

SELECT TO_UTC_TIMESTAMP_TZ('2021-08-24T12:59:35Z') - TO_UTC_TIMESTAMP_TZ('2021-08-24T12:59:05Z') AS RESULT
FROM DUAL;

Which returns this result:

RESULT
+000000000 00:00:30.000000000

Otherwise, if you want the number of seconds, you can incorporate CAST and ROUND to get the result:

SELECT ROUND((CAST(TO_UTC_TIMESTAMP_TZ('2021-08-24T12:59:35Z') AS DATE) - CAST(TO_UTC_TIMESTAMP_TZ('2021-08-24T12:59:05Z') AS DATE)) * 86400)  AS RESULT
FROM DUAL;

RESULT
30

Here is a DBFiddle showing both options (DBFiddle)