0

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 ?

Fredro
  • 7
  • 1
  • 1
    What answer - and data type - are you trying to get for those two starting values? And what data type are you starting from - strings? – Alex Poole Aug 24 '21 at 15:01

1 Answers1

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)

Del
  • 1,529
  • 1
  • 9
  • 18
  • In case you don't run Oracle 10 yet, use `TO_TIMESTAMP_TZ('2021-08-24T12:59:35Z', 'YYYY-MM-DD"T"HH:MI:SS.FFTZR')` instead of `TO_UTC_TIMESTAMP_TZ` – Wernfried Domscheit Aug 24 '21 at 18:05