formatting specific date to required format in not

问题:

Good day everyone,

I have a data set in a csv file that has the following date formatting:

Tue Jun 16 18:01:56 BST 2009
Thu Aug 20 09:40:51 BST 2015

I am working on a MySQL database where the data needs to be uploaded into a DATETIME column thus the required format is presented below. The timezone abbreviations need to be removed completely, I am able to do that with find/replace as well as day abbreviations. So only the month abbreviations need to be converted to numbers and the year placed in the beginning:

YYYY-MM-DD HH:MM:SS

I know I can use CTRL+F to find and replace specific values in NP++. But as you might've guessed the date combinations in the file can be different. I have researched that it can be accomplished with a regular expression but I am not familiar with that at all. Are there any expert users who can advise how I could write the necessary expression to format and replace my dates? Thank you in advance.

回答1:

You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:

UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
    CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
    '%b %d %H:%i:%s %Y'
)

Tests for STR_TO_DATE function