1

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?

  • GoldenGate is a Oracle product, so I would be very surprised if it would not have native support for TIMESTAMP WITH TIME ZONE values. – Wernfried Domscheit Jul 20 '18 at 6:44
  • @WernfriedDomscheit I know, and it is perplexing, but I heard from our Oracle support person, there is support, but with a not-so-small cost for every TZR to TZH:TZM mapping, which is done twice for every row – buræquete Jul 20 '18 at 6:57
0

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.

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.