Xrhrft

I have a Hibernate with Oracle JDBC based application inserting/updating into the following column;

COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL

but while it automatically appends timezone, it uses TZR, e.g. UTC, and weird thing with this data type in Oracle is that, it retains the format of timezone from insertion when we select it without any formatting, explained here

With this said, we are utilizing Golden Gate that is replicating this data to MongoDB, but afaik it requires these timestamps to contain only TZH:TZM formatting, so I have this issue where one side blocked by Oracle JDBC, where insertion is done with TZR, and one side blocked by Golden Gate where data is expected with TZH:TZM

Are there any way to handle this issue? Can I not forbid a certain formatting for TIMESTAMP WITH TIME ZONE ? Using NLS_TIMESTAMP_TZ_FORMAT works for SELECT formatting, but it is not usable for Golden Gate I was told. Also for INSERT case I was able to utilize default value of the column, and using systimestamp does insert with TZH:TZM, but for UPDATE case, I am unable to achieve this. Totally stuck!

I have this question with focus on application side of this issue. I am wondering if there is anything that can be done in DB side?

You could define your table like this.

CREATE TABLE TEST_A (

    COLUMN_A  

        TIMESTAMP(6) WITH TIME ZONE

        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00' 

        NOT NULL,

        CHECK (EXTRACT(TIMEZONE_REGION FROM COLUMN_A) = 'UNKNOWN')

    );



Table created.



INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM +00:00', 'DD-MON-RR HH.MI.SSXFF AM TZH:TZM'));



1 row created.



INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'));



>> INSERT INTO TEST_A VALUES (TO_TIMESTAMP_TZ('19-JUL-18 01.53.16.381566000 PM UTC', 'DD-MON-RR HH.MI.SSXFF AM TZR'))

Error at line 11

ORA-02290: check constraint (SYS_C00241787) violated

Or you create a table with trigger:

CREATE TABLE TEST_B (

    COLUMN_A  

        TIMESTAMP(6) WITH TIME ZONE

        DEFAULT SYSTIMESTAMP AT TIME ZONE '+00:00' 

        NOT NULL

    );



CREATE OR REPLACE TRIGGER BUIR_TEST_B

    BEFORE INSERT OR UPDATE ON TEST_B

    FOR EACH ROW

BEGIN

    IF EXTRACT(TIMEZONE_REGION FROM :NEW.COLUMN_A) <> 'UNKNOWN' THEN

        :NEW.COLUMN_A := :NEW.COLUMN_A AT TIME ZONE TO_CHAR(:NEW.COLUMN_A, 'tzh:tzm');

    END IF;

END;

/

However, I don't believe this would be a proper solution. You may face issues when you work with time zones having daylight saving times.