oracle日期筛选器未显示正确的日期结果

gg0vcinb  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(330)

我有数据

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

我只需要最短日期的数据。
所以我使用query作为(考虑东部和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

我不应该得到的(我不应该得到任何结果)
感谢您的帮助!

bjp0bcyl

bjp0bcyl1#

有两行样本数据。。。给你想要的解决方案带来挑战。但这里有一个查询,它为您提供了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;
8mmmxcuj

8mmmxcuj2#

美国/纽约时区是utc-4,所以当你把美国/纽约转换成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'

相关问题