Liquibase (3.6.3), not able to load oracle table column having datatype TIMESTAMP WITH TIMEZONE, It doesn't recognize the TZ factor

Description

Hi,

I am trying to load an oracle table (Oracle Version is 12c) which has a column with "TIMESTAMP WITH TIMEZONE", but looks like current version doesn't support it. It is not accepting TIMEZONE either with loaddata (csv file) or with insert command.

However for further drilling i have done a reverse engineering and created an insert changeset from an existing oracle table which has column TIMESTAMP WITH TIMEZONE and observed it truncates the existing TimeZone after exporting.

3st screenshot is the table record which has been exported to changeset (2nd screenshot). So it is evident that 3/22/2019 10:55:53.043297 PM -04:00 (from table) has been exported to 2019-03-23T10:55:53.043297 (in changeset).

Now if try to execute same insert changeset to load back to table, it creates this value in table as 3/23/2019 10:55:53.043297 AM +08:00 (1st screenshot).

That means existing 3/22/2019 10:55:53.043297 PM -04:00 will be stored in liquibase as 3/23/2019 10:55:53.043297 AM +08:00, which will give an incorrect version of baseline code (since i am trying to create base version of existing database in liquibase).

For reference, following is Changelog-dummy.xml (Insert changeset)

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet author="I604304 (generated)" id="1559065121218-1">
<insert tableName="PROCESS_GROUP">
<column name="PROCESS_GROUP_ID" valueNumeric="1"/>
<column name="GROUP_NAME" value="GLOBAL_XYZ"/>
<column name="DESCRIPTION" value="GLOBAL_XYZ"/>
<column name="INTERFACE_TYPE_ID" valueNumeric="6"/>
<column name="SENDING_SYSTEM_ID" valueNumeric="63"/>
<column name="DESTINATION_SYSTEM_ID" valueNumeric="64"/>
<column name="ASSET_CLASS_ID" valueNumeric="1"/>
<column name="DATA_FLOW_TYPE_IND" value="ORD"/>
<column name="DATA_FLOW_DIRECTION" value="I"/>
<column name="PUBLISH_METRIC_IND" value="N"/>
<column name="EFF_START_DATE" valueDate="2019-03-01T00:00:00"/>
<column name="EFF_END_DATE" valueDate="1999-12-31T00:00:00"/>
<column name="UPDATE_BY"/>
<column name="UPDATE_TS"/>
<column name="CREATE_BY" value="SAGEDBO"/>
<column name="CREATE_TS" type="TIMESTAMP(6) WITH TIME ZONE" valueDate="2019-03-23T10:55:53.043297"/>
</insert>
</changeSet>
</databaseChangeLog>

whenever i try to add timezone in CREATE_TS (of changelog-dummy.xml) something like <column name="CREATE_TS" valueDate="2019-03-23T10:55:53.043297 PM -04:00"/>, it throws error as following
Caused by: liquibase.exception.DatabaseException: ORA-00917: missing comma
[Failed SQL: INSERT INTO SAGE_UT.PROCESS_GROUP (PROCESS_GROUP_ID, GROUP_NAME, DESCRIPTION, INTERFACE_TYPE_ID, SENDING_SYSTEM_ID, DESTINATION_SYSTEM_ID, ASSET_CLASS_ID, DATA_FLOW_TYPE_IND, DATA_FLOW_DIRECTION, PUBLISH_METRIC_IND, EFF_START_DATE, EFF_END_DATE, UPDATE_BY, UPDATE_TS, CREATE_BY, CREATE_TS) VALUES (3, 'GLOBAL_XYZ', 'GLOBAL_XYZ', 6, 63, 64, 1, 'ORD', 'I', 'N', TO_DATE('2019-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('1999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL, NULL, 'SAGEDBO', 2019-03-23T10:55:53.043297 PM -04:00)]

Environment

Dev

Status

Assignee

Unassigned

Reporter

Arpit Agrawal

Labels

None

Affects versions

Priority

Major
Configure