MySQL: Import data (string) as time using LOAD DATA

Question

I have a dataset (in csv) which contains a column with value like "13.22.00". I would like to guess this is "hh.mm.ss".

How can I convert it to type time (it is string now) like hh:mm:ss?

Possible duplicate of How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE — TheAlexLichter
– TheAlexLichter, Commented Mar 7, 2016 at 23:05

Barmar · Accepted Answer · 2016-03-07 23:01:17Z

0

You can use the SET clause in LOAD DATA to transform the input data before putting it into the table. E.g. if the time is in column 2 of the CSV and you want to put the time into the time column of the table

LOAD DATA INFILE "filename"
(col1, @col2, col3, ...)
SET time = REPLACE(@col2, '.', ':');

answered Mar 7, 2016 at 23:01

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Purplehana Over a year ago

Thank you! I appreciate your help!

Collectives™ on Stack Overflow

MySQL: Import data (string) as time using LOAD DATA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related