Convert complex string to date-CodePudding

I have a table which pulls data from RSS Feeds and saves. The RSS feed format of representing publication date and time seems difficult to be converted into a MYSQL DATE(TIME) format. It comes as Thu, 14 Jan 2021 17:11:05 0000 for example. So when I use STR_TO_DATE to try converting it, it returns null since STR_TO_DATE doesn't go with such formats. Also, I can't even trim parts of the string and make it work with STR_TO_DATE. Of course, other functions like UNIX_TIMESTAMP won't work with that kind of string. How can I convert such a complex string to a MYSQL date format? I'm trying to do something like:

SELECT * FROM tbl WHERE 1 ORDER BY STR_TO_DATE('Thu, 14 Jan 2021 17:11:05  0000', '%d-%m-%Y')

CodePudding user response：

You need to complete your date format. You can truncate the parts you don't want afterwards.
As an alternative you could use substring before convertion.

select str_to_date(
'Thu, 14 Jan 2022 17:11:05  0000',
'%a, %d %b %Y %H:%i:%s  %f') formatted_date

| formatted_date             |
| :------------------------- |
| 2022-01-14 17:11:05.000000 |

db<>fiddle here