如何在Toad for Oracle中筛选时间戳差异?

huangapple go评论67阅读模式
英文:

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

问题

I have a Table called CO_EXCEPT_META_ST_LG under a Schema known as POMSUSERPRD.

In this 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.

英文:

I have a Table called CO_EXCEPT_META_ST_LG under a Schema known as POMSUSERPRD.

In this 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 POMSUSERPRD.CO_EXCEPT_META_ST_LG

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'.

答案1

得分: 1

你可以检查时间戳是否不相等:

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

对于示例数据:

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;

注意:最后一行是一个示例,其中列具有相同的UTC时间,但位于不同的时区,因此看起来不同,即使它们是相同的时间。

输出:

EXCEPT_ID DIFFERENCE
COM_002913 -000000000 00:00:00.001007000

fiddle

英文:

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

huangapple
  • 本文由 发表于 2023年6月27日 19:04:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76564217.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定