How to convert string to date time format Mysql

Question

I have this file csv:

20170117-00:00:6    3087A6B282A46C  124.191.134.139 2746    256 63628   
20170117-00:00:53   330391C9F58B5A  120.18.97.172   2746    257 43077
20170117-00:02:27   3303CD14BD40C8  110.147.131.78  2746    256 63628
20170117-00:02:00   3304B3CD448E4F  189.35.218.62   2746    255 43074

and I am trying to upload it using load data local infile but I obtain only Null value in the time column. Here there is my code (Time is the name of the table column)

LOAD DATA LOCAL INFILE 'path.log'
                INTO TABLE Clicks
                CHARACTER SET latin1
                FIELDS TERMINATED BY '\t'

                ENCLOSED BY '"'
                LINES TERMINATED BY '\n'
                IGNORE 1 ROWS

                SET Time= STR_TO_DATE(@Time,'%Y%m%d-%H:%i:%s')
                ;

Answer 1

This is the syntax you should be using:

LOAD DATA LOCAL INFILE 'path.log'
INTO TABLE Clicks
CHARACTER SET latin1
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@Time, column2, column3, column4, column5)         -- read time data into @Time
SET column1 = STR_TO_DATE(@Time, '%Y%m%d-%H:%i:%s') -- then set column1

I think the @Time variable was never being set to anything for each record being read, hence you were getting NULL in the first column.