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.


Posted

in

by

Tags:

Comments

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

  1. Randall Avatar

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

  2. psyborgison Avatar
    psyborgison

    thank you sir, for taking the time

  3. Monica Serrano Avatar
    Monica Serrano

    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?

  4. Josh Avatar

    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

Leave a Reply

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