Use an external utility to convert the dates to ISO format
before importing the data into MySQL (cvt_date.pl is useful here). Or use LOAD
DATA
’s capability for preprocessing input
data before it gets loaded into the database.
Suppose that you have a table that contains three columns,
name
, date
, and value
, where date
is a DATE
column requiring values in ISO format
(CCYY-MM-DD
). Suppose also that you’re
given a datafile newdata.txt to
be imported into the table, but its contents look like this:
name1 01/01/99 38 name2 12/31/00 40 name3 02/28/01 42 name4 01/02/03 44
Here the dates are in MM/DD/YY
format
and must be converted to ISO format to be stored as DATE
values in MySQL. One way to do this is
to run the file through the cvt_date.pl script shown earlier in the
chapter:
%cvt_date.pl --iformat=us --add-century newdata.txt > tmp.txt
You can then load the tmp.txt file into the table. This task also
can be accomplished entirely in MySQL with no external utilities by
using SQL to perform the reformatting operation. As discussed in Preprocessing Input Values Before Inserting Them, LOAD
DATA
can preprocess input values before inserting them. Applying that
capability to the present problem, the date-rewriting LOAD
DATA
statement looks like this, using the STR_TO_DATE()
function (Changing MySQL’s Date Format) to interpret the input
dates:
mysql>LOAD DATA LOCAL INFILE 'newdata.txt'
->INTO TABLE t (name,@date,value)
->SET date = STR_TO_DATE(@date,'%m/%d/%y'),
With the %y
format specifier
in STR_TO_DATE()
, MySQL
converts the two-digit years to four-digit years automatically, so the
original MM/DD/YY
values end up as ISO
values in CCYY-MM-DD
format. The resulting
data after import looks like this:
+-------+------------+-------+ | name | date | value | +-------+------------+-------+ | name1 | 1999-01-01 | 38 | | name2 | 2000-12-31 | 40 | | name3 | 2001-02-28 | 42 | | name4 | 2003-01-02 | 44 | +-------+------------+-------+
This procedure assumes that MySQL’s automatic conversion of two-digit years to four digits produces the correct century values. This means that the year part of the values must correspond to years in the range from 1970 to 2069. If that’s not true, you need to convert the year values some other way. (See Performing Validity Checking on Date or Time Subparts for some ideas.)
If the dates are not in a format that
STR_TO_DATE()
can handle, perhaps you can write a stored function to handle them and
return ISO date values. In that case, the LOAD
DATA
statement looks like this, where my_date_interp()
is the stored
function name:
mysql>LOAD DATA LOCAL INFILE 'newdata.txt'
->INTO TABLE t (name,@date,value)
->SET date = my_date_interp(@date);
3.145.18.101