Import null and improperly formatted datetime values into datetime column MySQL
I'm using a MySQL database with the Sequel Pro interface, and am new to SQL. I'm attempting to import data from a csv file and one of the columns I am importing into is of type datetime. However, the format I receive the data in is mm/dd/yy hh:mm AM/PM or null. Originally, I modified the type of the column to be varchar to avoid the issue but now I need to perform some date functions on the data that can't be done unless the column has a datetime type and format, so I need a way to convert the incoming data to the proper datetime format.
Additionally, people with no knowledge of SQL or databases are going to be running the import statement so it would be preferable to have them simply click file -- import and not have to enter anything complicated into the mysql command line. Also, after running a query I need to export the data in the same format it came in (mm/dd/yy hh:mm AM/PM or null).
Here are some sample values from the column:
Completion Time
null
6/16/14 10:33 AM
null
null
6/16/14 13:03 PM
6/17/14 13:53 PM
6/18/14 14:38 PM
6/18/14 14:52 PM
6/19/14 13:13 PM
6/18/14 18:56 PM
6/18/14 19:02 PM
null
A possibly simple solution that I've gathered might not be such a good idea from a couple of hours of googling, would be to keep the column type as varchar then somehow extract just the mm/dd/yy portion of the incoming data, convert that to proper MySQL date format and then perform my date functions.
Anyway any help would be greatly appreciated.
Answer
That's not very difficult with MySQLs date and time functions. STR_TO_DATE does what you need for the import:
the format I receive the data in is mm/dd/yy hh:mm AM/PM or null.
You get your DATETIME value with
STR_TO_DATE(yourValue, '%m/%d/%y %h:%i %p')
You find the specifiers for STR_TO_DATE at the description of the function DATE_FORMAT
For the export you do the reverse with the already mentioned function DATE_FORMAT with exact the same format string:
SELECT DATE_FORMAT(your_datetime_col, '%m/%d/%y %h:%i %p')
Have a look at this Demo
You can do the conversion at the INSERT statement like that:
INSERT INTO example (date_time) VALUES
(STR_TO_DATE('09/26/14 07:30 AM', '%m/%d/%y %h:%i %p'));
See it working in the updated Demo
Customizing an import with LOAD DATA INFILE
Let's have a table example with two columns id and date_time as
CREATE TABLE example (
id INT NOT NULL PRIMARY KEY,
date_time DATETIME
);
We have further a CSV file example.csv with data like that:
id,date
1,09/26/14 07:30 AM
2,07/23/14 07:30 PM
To import this file with LOAD DATA INFILE, you will use this statement:
LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- because of the column headers in the first line
(id, @var1) -- You've got to map every field of your csv file to a column
-- of your table.
-- You've got to list the names of the columns of your table,
-- not the headers in the csv file.
-- if one field should be ignored, use another variable for this
-- field.
SET date_time = STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p');
If your dates in the csv files contains the literal string 'null' that indicates a NULL value, then use the CASE operator:
date
09/26/14 07:30 AM
null
07/23/14 07:30 PM
then we've got to use
LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' -- your line endings
IGNORE 1 LINES -- because of the column headers in the first line
(@var1) -- read all parts of the date in variables
SET completionTime = CASE
WHEN @var1 = 'null' THEN NULL
ELSE STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p')
END;
The problem with fields containing the separator, in this case the comma, you already solved with enclosing those fields (or simply all) with an enclosing character.
But we really should have a look at your real format.