将多种日期时间格式转换为单个 mysql 日期时间格式 [英] Converting multiple datetime formats to single mysql datetime format

问题描述

不久前我聘请了一名自由职业者来解析一个具有日期时间字段的网站.在自由职业者完成后不久，我不得不将项目搁置一旁，现在我又回到了它，当我进入 MySQL 数据库时，我注意到了一些问题.

I hired a freelancer a little while ago to parse a website which had a datetime field. I had to put the project on the back burner shortly after the freelancer completed and now that I'm getting back into it, I've noticed some issues when I go into the MySQL database.

具体来说，有两种不同的日期时间格式，我不知道如何将它们更新为统一的 MySQL 可排序日期时间字段

Specifically, there are two different datetime formats and I can't figure out how to update them into a unified MySQL sortable datetime field

Apr 8 - 2:23 AM                                  <- for current year updates
Tue, Dec 2, 2014 06:06:00 PM          <- for all previous year updates
2014-12-02 06:06:00PM                    <- desired format

我在表格中有一个唯一的 ID，因此我可以轻松地选择和更新格式.所有Apr 8 - 2:23 AM"格式都是<例如'6340'.我还创建了一个date_proper"列来更新当前日期列.

I have a unique id in the table so I can select and update the formats easily. All "Apr 8 - 2:23 AM" format is < '6340' for example. I also have created a "date_proper" column to update the current date column.

我终其一生都无法找出将不同格式更新为相同统一格式的正确代码.任何帮助将不胜感激.

I just can't for the life of me figure out the correct code to update the different formats into the same unified format. Any help would be much appreciated.

推荐答案

为此，您需要使用 str_to_date().首先，修复 date_proper 列，使其成为 datetime.格式应该在输入和输出上处理.日期的正确存储是使用原生格式:

You need to use str_to_date() for this. First, fix the date_proper column so it is a datetime. Formats should be handled on input and output. The proper storage for dates is using native formats:

alter table modify date_proper datetime;

然后您可以更新值:

update t
    date_proper = (case when format < '6340'
                        then str_to_date(concat(year(now), ' ', col), '%Y %b %d - %h:%i %p'
                        else str_to_date(substr(col, 5), '%b %d, %Y %h:%i:%s %p'
                   end);

这篇关于将多种日期时间格式转换为单个 mysql 日期时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！