Search code examples
mysqlcsvmysqlimport

MySQL CSV Import - date entered as 0000-00-00 00:00:00 if timestamp has milliseconds?

I currently have a large number of CSVs to import to a MySQL database. The files contain timestamps for each record, which are in the format (for example): 

2011-10-13 09:36:02.297000000

I am aware of the MySQL bug #8523, which indicates that storing milliseconds in a datetime field is not supported. Despite this, I would have expected the datetime field to truncate the record after the seconds, instead of being entered as blank.

I have narrowed down the problem to the milliseconds (as opposed to the formatting of the csv etc.), since

2011-10-13 09:36:02

imports correctly.

Could anyone suggest a way that I can get this data imported without zeros? I have too many CSVs to go into each manually and adjust the length/formatting of the timestamps.

I should point out that while milliseconds would be a nice-to-have, they are not necessary to my application, so I would be happy with a solution that allows me to easily truncate the numbers and import them.

Thanks!

EDIT: To clarify, I am importing the CSVs using the following command:

mysqlimport --fields-enclosed-by="" --fields-terminated-by="," --lines-terminated-by="\n" --columns=id,@x,Pair,Time -p --local gain [file].csv

This is very fast for importing the records - I have around 50m to import, so reading each line in is not a great option.

Solution

  • I don't know how are you importing the CSVs but the way I would do is to write a script (php/perl) to read each file, round up or trim the time stamp to seconds and execute INSERT statements on the DATABASE.

    Something like

    <?php
$file=fopen("your.csv","r");
mysql_connect ($ip, $user, $pass);

while(!feof($file))
{
   $line = explode(',',fgets($file));
   mysql_query("INSERT INTO TABLE1 (ID, DATE) values (".$line[0].", ".substr($line[1],0,19).")");
}
fclose($file);
?>

    Execute this from the command line and it should do the job