我的csv导入没有格式化日期

Question

I cannot seem to get the csv file to format the dates to UTC time on import to mysql. Here is my query and if i take out the sql variables and the set statement it imports the information fine but i need the dates to be formatted in utc time.

$query = "
        LOAD DATA LOCAL INFILE '".$file."' INTO TABLE instructions
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '
'
        (route_name,stop_sequence_number,stop_location_id,stop_action,@stop_arrival_time,@stop_departure_time,time_zone);
        SET stop_arrival_time = STR_TO_DATE(@stop_arrival_time, '%m/%e/%Y %r');
        SET stop_departure_time = STR_TO_DATE(@stop_departure_time, '%m/%e/%Y %r');
        ";

Am I not using the STR_TO_DATE format correctly? i looked at the information on the format and it said that %m for numbered month, %e for single digit day, %y for four length year and the %r for AM - PM time.

Answer 1

You do not have to convert the string to a date in order to import the date. As you have noticed it will except the statement after you have removed the STR_TO_DATE part of the import statement. However, you will instead get a bunch of columns looking like 0000-00-00 because mysql does not understand dates in mm/dd/yyyy format, etc... Mysql only excepts dates in YYYY-MM-DD format.

You can change this in Excel before doing your import.

In order to change the date format in excel: right click on the top cell. Choose format cells from the drop down list. change the local to something like 'Afrikans'. Choose the format that looks like 2001-03-14. Use the top cell to fill down. Then save the document.

Just a quick note: Excel sometimes tries to do too much and will revert this column back to a the English(U.S) default time zone. So, if you plan on doing more editing make sure that the column has not reverted back.

Here is a link to more string literals on dev.mysql.

This stored procedure and post might help you as well: Error code 1292

Edit:

Alex, your code has 3 semi-colons in it. Have you tried your original code as shown by you without the first two but keeping the last? As such it would be consistent with this Answer.