Date format for LOAD DATA LOCAL INFILE

I’m loading data into a MySQL table with PHP using LOAD DATA LOCAL INFILE. There are date fields in the import file that are in the format 10/11/2021 02:49 PM America/New York. How do I deal with the time zone text at the end? I have tried stripping it off, but can’t get it to work. The date comes in as 0000-00-00 00:00:00 every time.

Variations I have tried….

SET revision_date = STR_TO_DATE(LEFT(@revision_date, 19), '%m/%d/%Y %h:%i:%s %A'),
SET revision_date = STR_TO_DATE(SUBSTR(@revision_date, 0, 19), '%m/%d/%Y %h:%i:%s %A'),
SET revision_date = STR_TO_DATE(SUBSTR(@revision_date, 0, -17), '%m/%d/%Y %h:%i:%s %A'),

The only thing I’ve been able to get to work is stripping the time zone off in the csv file before importing and using…

SET revision_date = STR_TO_DATE(@revision_date, '%m/%d/%Y %h:%i:%s %A'),

New data will be loaded frequently, and there are several thousand line of data in the CSV file, times three date fields, so I really would like to deal with it on the import if possible.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The sample date you have given is using a 12-hour clock with AM/PM. For that specific date you can use

SET revision_date = str_to_date('10/11/2021 02:49 PM America/New York','%m/%d/%Y %h:%i %p');

which returns 2021-10-11 14:49:00

Note %p, not %A for the AM/PM indicator.

I’m assuming all dates are in the same format (no mixed 12-hour and 24-hour clock or US/European date formats), and that you’re familiar with LOAD DATA INFILE syntax.

Method 2

Answering the question with my solution for future reference. Date data was loaded into table as is, into a VARCHAR() field. Then re-formatted into a date format by stripping off the time zone text, when the table was queried for that field.

$revision_date = date('Y-m-d H:i:s', strtotime(substr($revision_date, 0, 19)));

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0