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.

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

$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

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.297000000I am aware of th... MODIFY COLUMN paidTime datetime null; // 允许空值 load data local infile 'D:/MyDownloads/order_info_utf. csv ' into table practice.orderinfo fields terminated by ','; // 用这行代码 导入 数据比 time_str = '2023-03-21 23:45: 00 ' time_obj = datetime.datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S') timestamp = int(time_obj.timestamp()) print(timestamp) 输出结果为： 16870767 00 其中，`strptime`函数用于将字符串转换为datetime对象，`timestamp`函数用于将datetime对象转换为 时间戳 。