MySQL Bugs: #12754: TIMEZONE WITH (LOCAL) TIME ZONE can't be converted

Bug #12754	TIMEZONE WITH (LOCAL) TIME ZONE can't be converted
Submitted:	23 Aug 2005 12:31	Modified:	30 Aug 2005 21:45
Reporter:	Sadao Hiratsuka (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.13	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
Following two types can't be converted from Oracle to MySQL.
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE

How to repeat:
-- Oracle test data

create table test008_date (
col1 date,
col2 timestamp(9),
col3 timestamp(9) with time zone,
col4 timestamp(9) with local time zone
)
/
insert into test008_date values (
to_date('2005/08/23 18:43:35', 'yyyy/mm/dd hh24:mi:ss'),
to_timestamp('2005/08/23 18:43:35.123456789', 'yyyy/mm/dd hh24:mi:ss.ff9'),
to_timestamp_tz('2005/08/23 18:43:35.123456789 +9:00', 'yyyy/mm/dd hh24:mi:ss.ff9 tzh:tzm'),
to_timestamp('2005/08/23 17:43:35.123456789', 'yyyy/mm/dd hh24:mi:ss.ff9')
)
/
commit
/

-- Generated Creates.sql

CREATE TABLE `mi`.`test008_date` (
  `col1` DATETIME NULL,
  `col2` TIMESTAMP NULL,
  `col3` TIMESTAMP NULL,
  `col4` TIMESTAMP NULL
)
ENGINE = INNODB;

-- Generated Inserts.sql

INSERT INTO `mi`.`test008_date`(`col1`, `col2`, `col3`, `col4`)
VALUES ("2005-08-23 18:43:35", "2005-08-23 18:43:35", "", "");

-> col3 and col4 are empty.

Suggested fix:
These two types shuld be handled as TIMESTAMP.
(Of course it is necessary to take care of TIMEZONE.)

In migrateColumnToMysql, Toolkit uses indexOf method to determine data type.
-> if (sourceDatatypeName.indexOf("TIMESTAMP") > -1)

But in doDataBulkTransferTableToMysql, it uses equalsIgnoreCase method.
-> columnTypeName.equalsIgnoreCase("TIMESTAMP")

And maybe,
Oracle TIMESTAMP type corresponds to MySQL DATETIME type,
not to MySQL TIMESTAMP type.

Thanks for your bug report.  I was able to reproduce this with 1.0.15

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Note that the timezone info is not considered because the Oracle 9 JDBC driver does not support it yet. So the normal datetime value is taken instead. Oracle TIMESTAMP is now correctly converted to MySQL DATETIME.

From the Oracle docs:
The Oracle JDBC drivers do not support the Calendar datatype because it is not yet feasible to support java.sql.Date timezone information. Calendar input to setXXX() or getXXX() method calls for Date, Time, and Timestamp is ignored. The Calendar type will be supported in a future Oracle release.