How to automatically convert timezone while loading into MySQL

user_12245574 0
Viewed 312

I am trying to load a table which has DATETIME datatype in MySQL. The source is a csv file which has values in UTC. Is there a way where i can mention to automatically convert them to EST while loading data.

I am using "LOAD DATA from S3" in AWS to load data into Aurora Mysql.

Below is a sample for one column. 

 birth_dt in UTC     =+ birth_dt in table as EST
 =+=+=+=+=+=+=+=+=+  =+=+=+=+=+=+=+=+=+=+=+=+=+=+
1990-09-20 23:00:00  =+   1990-09-20 19:00:00
 =+=+=+=+=+=+=+=+=+  =+=+=+=+=+=+=+=+=+=+=+=+=+ ```
csv datetime mysql
1 Answers

Before inserting directly into the column store in a temporary variable then convert into 'EST' and insert.

Here is the sample load file use the set line and change as per your table and column name.

LOAD DATA LOCAL INFILE 'path of csv' INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(col1,col2,....,@utc)
SET date_column = CONVERT_TZ(@utc,'+0:00','-5:00');
user_103425140
Related