I'm not familiar with Oracle or PostgreSQL, but I know that a Timestamp data type remains constant
regardless of the time zone. In other words, the time zone portion of the data is optional.
Maybe there is a function in PostgreSQL that will convert the field to a plain Timestamp. You could
use that function to export the data to another table.
Gautam Deb wrote:
To add on that -
I have modified the fieldtypeoracle.xml file to change the mapping for
"date-time " as
<field-type-def type="date-time" sql-type="TIMESTAMP WITH TIME ZONE"
java-type="java.sql.Timestamp"></field-type-def>
This is required as we are migrating our existing data (from PostgreSQL)
which contains many of the fields as TIMESTAMPTZ.
Also if anybody can suggest a way to get rid of the timezone part while
migrating to Oracle (of-course maintaining the accuracy in date).
Thanks..
Gautam
Gautam Deb wrote:
I am trying to start OFBiz with Oracle 10g as back-end database. While
startup Oracle throws an error -
2007-06-12 12:53:08,056 (main) [DatabaseUtil.java:checkDb:364 :ERROR]
:: Could not create table [s2.AGREEMENT_PROMO_APPL]: SQL Exception
while executing the following:
CREATE TABLE s2.AGREEMENT_PROMO_APPL (AGREEMENT_ID VARCHAR2(20) NOT
NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR2(20) NOT NULL, PRODUCT_PROMO_ID
VARCHAR2(20) NOT NULL, FROM_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
THRU_DATE TIMESTAMP WITH TIME ZONE, SEQUENCE_NUM NUMBER(18,0),
LAST_UPDATED_STAMP TIMESTAMP WITH TIME ZONE, LAST_UPDATED_TX_STAMP
TIMESTAMP WITH TIME ZONE, CREATED_STAMP TIMESTAMP WITH TIME ZONE,
CREATED_TX_STAMP TIMESTAMP WITH TIME ZONE, CONSTRAINT
PK_AGREEMENT_PROMO_APPL PRIMARY KEY (AGREEMENT_ID,
AGREEMENT_ITEM_SEQ_ID, PRODUCT_PROMO_ID, FROM_DATE))
Error was: java.sql.SQLException: ORA-02329: column of datatype
TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key
This is happening for other entities also which have a timestamp field
as part of their keys.
Any help would be appreciated.
Regards,
Gautam Deb
