MySQL Load Data Infile – Epoch time to Timestamp

Mar 19, 2012

—

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.

Comments

4 responses to “MySQL Load Data Infile – Epoch time to Timestamp”

Randall

March 24, 2017

Thanks, that was helpful. Saved me the headache of figuring it out.
psyborgison

April 19, 2018

thank you sir, for taking the time
Monica Serrano

August 2, 2021

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?
Josh

August 2, 2021

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

MySQL Load Data Infile – Epoch time to Timestamp

Comments

4 responses to “MySQL Load Data Infile – Epoch time to Timestamp”

Leave a Reply