英文:
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 |
英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论