Sample Header Ad - 728x90

timestamp with timezone issue with TZR vs TZH:TZM

1 vote
1 answer
5284 views
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](https://stackoverflow.com/questions/51417100/timestamp-with-timezone-retains-timezone-format) 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](https://stackoverflow.com/questions/51412424/update-column-to-default-value-without-specifying-it-in-spring-data-jpa) with focus on application side of this issue. I am wondering if there is anything that can be done in DB side?
Asked by bvrakvs (111 rep)
Jul 19, 2018, 06:18 AM
Last activity: Jul 19, 2025, 03:08 AM