ORACLE日期过滤器未显示正确的日期结果

Question

我有数据

ID      MYDATE
1       2020-02-02 19:45:00:00
1       2020-02-02 20:00:00:00

我只需要获取min_date的数据。

所以我使用查询作为(考虑东部和utc时区(

SELECT  ID, MYDATE 
FROM MYTABLE
WHERE TO_CHAR(FROM_TZ(TO_TIMESTAMP(TO_CHAR(MYDATE.'YYYY-MM-DD HH24.MI.SS'),'YYYY- 
MM-DD HH24.MI.SS), 'AMERICA/NEW_YORK) AT TIME ZONE 'UTC', 'YYYYMMDD') = 
'20200202'

我得到了2020-02-02的结果(这是意料之中的(

1   2020-02-02 19:45:00:00

但当我竞选日期"20200203"时，我得到了

1      2020-02-02 20:00:00:00

我不应该得到(我不应该获得任何结果(

感谢您的帮助！

Answer 1

有两行样本数据，它是。。。很难给出您正在寻找的解决方案。但这里有一个查询，它为您提供id的最低日期行：

WITH tab (id, mydate) 
AS
(
SELECT 1, TO_DATE('2020-02-02 19:45:00','YYYY-MM-DD HH24:MI:SS') FROM DUAL union
SELECT 1, TO_DATE('2020-02-02 20:00:00','YYYY-MM-DD HH24:MI:SS') FROM DUAL  
),
ordered_dates AS
(
SELECT 
id, 
mydate, 
ROW_NUMBER() OVER (PARTITION BY id ORDER BY mydate) AS rn FROM tab
)
SELECT id, TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS') FROM ordered_dates WHERE rn = 1;

Answer 2

AMERICA/NEW_YORK时区是UTC-4，所以当您将AMERICA/NEW_YORK转换为UTC时，您会得到原始时间+4小时，即2020-02-03 01:00:00 UTC:

WITH t (id, mydate) 
AS
(
SELECT 1, TO_DATE('2020-02-02 19:45:00','YYYY-MM-DD HH24:MI:SS') FROM DUAL union
SELECT 2, TO_DATE('2020-02-02 20:00:00','YYYY-MM-DD HH24:MI:SS') FROM DUAL  
)
select
TO_CHAR(MYDATE,'YYYY-MM-DD HH24.MI.SS') original_time,
FROM_TZ(
TO_TIMESTAMP(
TO_CHAR(MYDATE,'YYYY-MM-DD HH24.MI.SS')
,'YYYY-MM-DD HH24.MI.SS'
),
'AMERICA/NEW_YORK'
)
AT TIME ZONE 'UTC'
as UTC_TIME
from t;
ORIGINAL_TIME       UTC_TIME
------------------- --------------------------------------
2020-02-02 19.45.00 2020-02-03 00:45:00.000000000 UTC
2020-02-02 20.00.00 2020-02-03 01:00:00.000000000 UTC
2 rows selected.

在这种情况下，您不需要to_timestamp(to_char(...，使用CAST:更容易

FROM_TZ(cast(MYDATE as timestamp),'AMERICA/NEW_YORK') AT TIME ZONE 'UTC'