MySQL Load Data Infile – Epoch time to Timestamp
By : Josh -
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.
Thanks, that was helpful. Saved me the headache of figuring it out.
thank you sir, for taking the time
Hello!
I have a table with a lot of fields. Three of them are timestamp (me_fecha, me_fechamedestado, me_fechadecreacion). I want to load the data into my table from a .csv file. My code is:
LOAD DATA LOCAL INFILE ‘C:/Users/Moni/Desktop/PFB/Data/tbl_medicion_eje_202107051122.csv’
INTO TABLE Tbl_medicion_eje
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’
set timestampfield = FROM_UNIXTIME( @me_fecha, @me_fechamedestado, @me_fechadecreacion )
IGNORE 1 LINES;
I don’t know where is the mistake. Could you help me, please?
Hi Monica,
What is the error you are receiving? Also, it would be helpful if you could provide some sample data – please do not post any sensitive or proprietary information.
Thanks,
Josh