CODEWITHSUNDEEP
mysqlunixunix-timestamp
Sanjoy Dey
Sanjoy Dey

Reputation: 48

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")'

Upvotes: 1

Views: 233

Answers (2)

Akina
Akina

Reputation: 42632

SET @time := '2020-10-07T05:21:05-7:00';
SELECT CONVERT_TZ(STR_TO_DATE( @time, '%Y-%m-%dT%H:%i:%s'),            -- datetime only
                  REGEXP_SUBSTR(@time, '[\+\-][0-9]{1,2}:[0-9]{2}$'),  -- timeshift only
                  '+00:00') AS datetime_GMT;

fiddle

Upvotes: 1

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can try this:

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

Upvotes: 1

Related Questions