I need to compute the Elapsed Time (using UTC as a common global reference) for a worldwide application.
My cache table gets updated periodically. That update can be triggered from anywhere in the world. So, upon update, I post the UTC time into the TBL_CACHE_STATUS in a TIMESTAMP(6) field. Later, I want to compare that time to the Current UTC to see the ELAPESED TIME.
While not a novice by any means, I have always hated working with dates and timestamps. Both Oracle and SQL server do some things that are obvious and others that are not when it comes to date manipulation. Generally, you cannot simply "add" or "subtract" dates. However, I have seen some articles on date manipulation in Oracle where they do that. Specifically, they did select (sysdate - (sysdate - 1)) * 24 * 60 * 60 from dual; to get the 86,400 seconds elapsed in one day as there example. This runs exactly as described and expected in Oracle.
So, I implemented the example here:
SELECT
CACHE_ACTION,
CACHE_UPDATED_UTC,
UTC.CURRENT_UTC,
(UTC.CURRENT_UTC-CACHE_UPDATED_UTC) ELAPSED_HOURS
FROM TBL_CACHE_STATUS, (SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' CURRENT_UTC FROM DUAL) UTC
The result is:
CACHE_ACTION, CACHE_UPDATED_UTC, CURRENT_UTC, ELAPSED_HOURS
1, 15-MAR-18 04.13.16.546191000 PM, 15-MAR-18 04.19.54.183209000 PM UTC, -04 21:20:56.711568
2, 14-MAR-18 08.27.49.797614000 PM, 15-MAR-18 04.19.54.183209000 PM UTC, +14 20:49:45.254280
3, 14-MAR-18 08.24.41.932365000 PM, 15-MAR-18 04.19.54.183209000 PM UTC, +14 22:04:54.020256
So, you can see that Cache_Action=1 was updated about six minutes before the current UTC time. When I tried computing the difference, it makes no sense.
Is the ONLY way to "parse" out the Year, Month, Day, Hour, Minute and Seconds and then computing the specific differences yourself? Is there no good Oracle option to "subtract" a date from another? Do TIMESTAMP and DATE work differently?
Rob