0

(Oracle 12c geodatabase)

I have a view that converts a UTC timestamp to Eastern Time using the from_tz function:

create or replace view test_vw as (
select
    cast(rownum as number(38,0)) as rownum_,
    from_tz(
         cast(to_date('2019/11/02 05:00:00', 'yyyy/mm/dd HH24:MI:SS') as timestamp)      
    , 'UTC') at time zone 'US/Eastern' as my_timestamp
from
    dual
);

   ROWNUM_ MY_TIMESTAMP                       
---------- -----------------------------------
         1 11/02/2019 1:00:00.000000 AM -04:00

In Toad, the SQL converts 5:00 am UTC to 1:00 am Eastern Time.

That is the expected result.


However, when I use the view in ArcMap 10.7.1, the timestamp does not display the correct time:

enter image description here

It displays 5:00 am UTC, instead of 1:00 am Eastern Time.


Is there a way to display the converted time correctly in Arcmap?

0

I can convert the offset timestamp to a character, then back to a timestamp.

It's pretty ugly. But from a cursory look, it seems to work.


A simple, non-timezone related example:

to_timestamp(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')

The actual solution:

create or replace view test_vw as (
select
    cast(rownum as number(38,0)) as rownum_,
    to_timestamp(to_char(
        from_tz(
            cast(to_date('2019/11/02 05:00:00', 'yyyy/mm/dd HH24:MI:SS') as timestamp)      
        , 'UTC') at time zone 'US/Eastern' 
    , 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') as my_timestamp
from
    dual
);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.