More than 60 seconds or minutes in MySQL import fi

问题:

I'm currently importing a CSV file to a MySQL table, with lines like:

2/28/2015 8:46:30 PM,1:40,2,1234567,1435.6071
2/28/2015 8:45:58 PM,0:19,1,1234568,1435.6436

It's basically a CDR from a PBX. However, I don't have control of info source, so I just receive this file as is. So far, columns are:

DATE,DURATION,MINUTES,PHONE,BALANCE

I've used LOAD DATA INFILE to import my data, like this:

LOAD DATA INFILE '10811.csv'
INTO TABLE cdr
fields terminated by ','
lines terminated by '\n'
(@col1, @col2, MINUTES, PHONE, BALANCE)
set DATE = STR_TO_DATE(@col1,'%m/%d/%Y %h:%i:%s %p'),
DURATION = STR_TO_DATE(@col2,'%i:%s'),
CARDID = 10811;

CARDID is just a field to store my source filename

The problem is: DURATION is giving me problems. See: Some lines are like this:

2/28/2015 8:46:30 PM,140:28,141,1234569,1435.6071

That means, the duration is 140 minutes, 28 seconds; however, if I store that value, it gives me an error. This because:

> SELECT str_to_date('140:28','%i:%s') AS DATE;
+------+
| DATE |
+------+
| NULL |
+------+

Because %i expects minutes between 00 and 59.

I've tried to use SEC_TO_TIME, but also fails (it just takes first number):

> SELECT sec_to_time('140:28') AS DATE;
+----------+
| DATE     |
+----------+
| 00:02:20 |
+----------+

How could I modify my query (please, just SQL) to store that time in correct format?

回答1:

Unlike there are hour format specifier "%H" and "%k" accepting values larger than range 0-23, there is no minute format specifier accepting values larger than range 0-59.

You can use string function provided by MYSQL to convert the DURATION field into proper format(like 140:28 -> 2:20:28).

But better practice is using other tools/language/script, which is more efficient and easier than MYSQL's string functions at string manipulation, to do the string conversion.

回答2:

I've managed to fix this with a string manipulation. It's not the most elegant solution, but hey! it works!

LOAD DATA INFILE '10811.csv'
INTO TABLE cdr
fields terminated by ','
lines terminated by '\n'
(@col1, @col2, MINUTES, PHONE, BALANCE)
set DATE = STR_TO_DATE(@col1,'%m/%d/%Y %h:%i:%s %p'),
DURATION = SEC_TO_TIME(SUBSTRING_INDEX(@col2,':',1)*60+SUBSTRING_INDEX(@col2,':',-1))
CARDID = 10811;