MySQL Load Data Infile – Epoch time to Timestamp

I was recently working on a data load where I wanted to convert one column from an epoch time format into a timestamp column on import. I couldn’t figure out why the timestamp was being set to the current time on every test, no matter what I set the variables/values to.

I initially tried with this script:

for file in $(find ${FILESRC} -maxdepth 1 -type f -name "*csv")
do

mysql -u user --password=password << EOF
LOAD DATA INFILE "${file}"
  INTO TABLE mydb.mytable
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  (field1,field2,@epochtime)
  set timestampfield = FROM_UNIXTIME( @epochtime ) 
;
EOF

#mv ${file} ${FILEDST}/

done

Note that this is loading into a table with at least 3 columns, field1, field2, and timestampfield.

The problem was that when I created the table, the timestamp field default value was set to CURRENT_TIMESTAMP and was over-riding the SET operation.

To fix the issue, I changed the data type for the timestamp field to datetime which has a default of NULL and the import then worked.

2 thoughts on “MySQL Load Data Infile – Epoch time to Timestamp”

Leave a Reply

Your email address will not be published. Required fields are marked *