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
Last activity: Jul 19, 2025, 03:08 AM