Search code examples
mysqlunixunix-timestamp

How to convert timestamp from Oracle to Mysql

I'm trying to convert timestamp from Oracle to Mysql. I have a data text file, trying to load data in MySQL database but getting an SQL syntax error. Please help

LOAD DATA INFILE '/check/value.txt' INTO TABLE TEST FIELDS TERMINATED BY '|' (ID, @DATETIME) SET DATETIME = CONVERT_TZ(REPLACE(SUBSTRING_INDEX(@DATETIME, '-', 3),"T"," "), "-07:00", "+00:00");

timestamp example - 2020-10-07T05:21:05-7:00.

Below is the error

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET DATETIME = CONVERT_TZ(REPLACE(SUBSTRING_INDEX(@DATETIME, '-', 3),"T"," "), "-07:00", "+00:00")'
Solution

  • You can try this:

    SELECT CONVERT_TZ(REPLACE(SUBSTRING_INDEX("2020-10-07T05:21:05-7:00", '-', 3),"T"," "), "-07:00", "+00:00");