Description:
I used the Migration Toolkit to migrate the database from Oracle to MySQL.
The data of timestamp with local time zone is not converted correctly.
How to repeat:
Create the table in Oracle as follow.
****************************************************************************
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;
SQL> select * from test008_date;
COL1 COL2 COL3 COL4
--------------- ------------------------------ ----------------------------------- -----------------------------------
05-08-23 05-08-23 18:43:35.123456789 05-08-23 18:43:35.123456789 +09:00 05-08-23 17:43:35.123456789
****************************************************************************
Oracle has the correct data.
Convert the table by Migration Toolkit.
Creates and Inserts logs are there.
****************************************************************************
[Creates Log]
-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;
CREATE DATABASE IF NOT EXISTS `mi`
CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `mi`;
-- -------------------------------------
-- Tables
DROP TABLE IF EXISTS `mi`.`test008_date`;
CREATE TABLE `mi`.`test008_date` (
`col1` DATETIME NULL,
`col2` DATETIME NULL,
`col3` DATETIME NULL,
`col4` DATETIME NULL
)
ENGINE = INNODB;
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------------------------------------------------
-- EOF
****************************************************************************
[Inserts Log]
-- ----------------------------------------------------------------------
-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------
-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
INSERT INTO `mi`.`test008_date`(`col1`, `col2`, `col3`, `col4`)
VALUES ('2005-08-23 18:43:35', '2005-08-23 18:43:35', '2005-08-23 18:43:35', '1970-01-01 09:00:00');
-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
-- End of script
****************************************************************************
The date of timestamp with local time zone is not correct.
Suggested fix:
I
Description: I used the Migration Toolkit to migrate the database from Oracle to MySQL. The data of timestamp with local time zone is not converted correctly. How to repeat: Create the table in Oracle as follow. **************************************************************************** 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; SQL> select * from test008_date; COL1 COL2 COL3 COL4 --------------- ------------------------------ ----------------------------------- ----------------------------------- 05-08-23 05-08-23 18:43:35.123456789 05-08-23 18:43:35.123456789 +09:00 05-08-23 17:43:35.123456789 **************************************************************************** Oracle has the correct data. Convert the table by Migration Toolkit. Creates and Inserts logs are there. **************************************************************************** [Creates Log] -- ---------------------------------------------------------------------- -- MySQL Migration Toolkit -- SQL Create Script -- ---------------------------------------------------------------------- SET FOREIGN_KEY_CHECKS = 0; CREATE DATABASE IF NOT EXISTS `mi` CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `mi`; -- ------------------------------------- -- Tables DROP TABLE IF EXISTS `mi`.`test008_date`; CREATE TABLE `mi`.`test008_date` ( `col1` DATETIME NULL, `col2` DATETIME NULL, `col3` DATETIME NULL, `col4` DATETIME NULL ) ENGINE = INNODB; SET FOREIGN_KEY_CHECKS = 1; -- ---------------------------------------------------------------------- -- EOF **************************************************************************** [Inserts Log] -- ---------------------------------------------------------------------- -- SQL data bulk transfer script generated by the MySQL Migration Toolkit -- ---------------------------------------------------------------------- -- Disable foreign key checks SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; INSERT INTO `mi`.`test008_date`(`col1`, `col2`, `col3`, `col4`) VALUES ('2005-08-23 18:43:35', '2005-08-23 18:43:35', '2005-08-23 18:43:35', '1970-01-01 09:00:00'); -- Re-enable foreign key checks SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -- End of script **************************************************************************** The date of timestamp with local time zone is not correct. Suggested fix: I