Search code examples
sql

How to SQL Filter for Timestamp Differences in Toad for Oracle?

In my Table, there are two Columns:

  • LAST_CHANGED_DATE
  • LOG_ENTRY_TIMESTAMP

The Data Type for both is listed as TIMESTAMP(6) WITH TIMEZONE. The Entries for both Columns have the format: 05/03/2019 09:37:35.849116 +00:00 (The +00:00 changes to +01:00 for Daylight Savings)

Usually, I have to format timestamps as below when using commands like < or >: TO_TIMESTAMP_TZ('14/11/2021 20:32:07.165000 +01:00', 'DD/MM/YYYY HH24:MI:SS.FF TZH:TZM')

The Table has entries like the following:

EXCEPT_ID LAST_CHANGED_DATE LOG_ENTRY_TIMESTAMP
COM_000001 05/03/2019 09:37:35.849116 +00:00 05/03/2019 09:37:35.849116 +00:00
DEV_000003 12/03/2019 17:13:54.187714 +00:00 12/03/2019 17:13:54.187714 +00:00
DEV_000007 08/04/2019 11:38:04.267723 +00:00 08/04/2019 11:38:04.267723 +00:00
COM_002913 28/09/2022 22:00:26.654732 +01:00 28/09/2022 22:00:26.655739 +01:00

Normally, there is no difference between the columns LAST_CHANGED_DATE and LOG_ENTRY_TIMESTAMP for any Entry. However, occasionally there is; as can be seen for EXCEPT_ID - COM_002913 in the above Table.

Is there a way for me to extract all entries from this table where the difference between the LAST_CHANGED_DATE and LOG_ENTRY_TIMESTAMP columns is not '0'? (If we were to pretend that the example table above is the full data set, the Answer would be COM_002913).

I have written the below query which provides me the difference between the timestamps for all entries:

SELECT EXCEPT_ID, LAST_CHANGED_DATE - LOG_ENTRY_TIMESTAMP AS Difference

FROM table_name

But this gives me over 14, 000 Entries. (COM_002913's Difference is expressed as -00 00:00:00.001007)

I tried adding a WHERE Clause (WHERE DIFFERENCE <> '0') but I received the following error:

ORA-06553: PLS-306: wrong number or types or arguments in call to 'OGC_DIFFERENCE'.

Solution

  • You can check if the timestamps are not equal:

    SELECT EXCEPT_ID,
       LAST_CHANGED_DATE - LOG_ENTRY_TIMESTAMP AS difference
FROM   POMSUSERPRD.CO_EXCEPT_META_ST_LG
WHERE  LAST_CHANGED_DATE != LOG_ENTRY_TIMESTAMP

    Which, for the sample data:

    CREATE TABLE CO_EXCEPT_META_ST_LG (EXCEPT_ID, LAST_CHANGED_DATE, LOG_ENTRY_TIMESTAMP) AS
SELECT 'COM_000001', TIMESTAMP '2019-03-05 09:37:35.849116 +00:00', TIMESTAMP '2019-03-05 09:37:35.849116 +00:00' FROM DUAL UNION ALL
SELECT 'DEV_000003', TIMESTAMP '2019-03-12 17:13:54.187714 +00:00', TIMESTAMP '2019-03-12 17:13:54.187714 +00:00' FROM DUAL UNION ALL
SELECT 'DEV_000007', TIMESTAMP '2019-04-08 11:38:04.267723 +00:00', TIMESTAMP '2019-04-08 11:38:04.267723 +00:00' FROM DUAL UNION ALL
SELECT 'COM_002913', TIMESTAMP '2022-09-28 22:00:26.654732 +01:00', TIMESTAMP '2022-09-28 22:00:26.655739 +01:00' FROM DUAL UNION ALL
SELECT 'DEV_000008', TIMESTAMP '2019-04-08 11:38:04.267723 +00:00', TIMESTAMP '2019-04-08 12:38:04.267723 +01:00' FROM DUAL;

    Note: The final row is an example where the columns have the same UTC time but are in different time zones so appear to be different even though they are the same time.

    Outputs:

    EXCEPT_ID DIFFERENCE
    COM_002913 -000000000 00:00:00.001007000

    fiddle