Recipe 10.33. Importing Non-ISO Date Values


Problem

Date values to be imported are not in the ISO (CCYY-MM-DD) format that MySQL expects.

Solution

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.

Discussion

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 Section 10.11, 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 (Section 6.2) 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 Section 10.30 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);             




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net