在记事本++中将特定日期格式化为所需格式 [英] formatting specific date to required format in notepad++

问题描述

大家好，

我在具有以下日期格式的csv文件中设置了一个数据集:

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

我正在使用一个MySQL数据库，该数据库需要将数据上传到 DATETIME 列，因此所需的格式如下所示.时区缩写需要完全删除，我可以使用查找/替换以及日期缩写来做到这一点.因此，仅需要将月份缩写词转换为数字，并将年份置于开头:

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

我知道我可以使用CTRL + F查找和替换NP ++中的特定值.但是，您可能已经猜到文件中的日期组合可能有所不同.我已经研究过可以用正则表达式来完成它，但是我一点都不熟悉.是否有任何专家用户可以建议我如何编写必要的表达式以格式化和替换日期?预先谢谢你.

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.

推荐答案

您可以按原样在临时的varchar列中导入数据，然后使用 STR_TO_DATE 将其转换为日期时间:

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'
)

STR_TO_DATE 函数的测试

这篇关于在记事本++中将特定日期格式化为所需格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！