mysql - 将文本时间戳转换为 MySQL 中所有行的日期时间

Question

我将数据和模式从 XML 文件导入到 MySQL 中。我在很多表格中都有以下列:

DateStart     tinytext
DateEnd       tinytext

这个格式看起来像一个时间戳，即。 2006-06-01T00:00:00-05:00其中一些值也是 null。我需要一种方法将此 tinytext 时间戳转换为普通的 MySQL datetime 但我不确定如何操作。

我尝试创建一个 my_date_time 类型的新列，并尝试像这样转换每个值:

UPDATE table SET my_date_time = str_to_date(DateStart, '%Y-%m-%d');

但是这给了我以下错误:

Data truncation: Incorrect date value: '2006-09-01T00:00:00-05:00' for column 'DateStart' at row 1

我应该如何为这些列转换所有行中的所有值？

Answer 1

您可以创建一个新字段:

SELECT
  DateStart,
  DateEnd,
  CAST(DateStart AS DATETIME) AS my_date_time
FROM [your_table]

或者您可以使用以下方法向您的表中添加一个新列:

UPDATE [your_table] SET my_date_time = CAST(DateStart AS DATETIME);

如果您只想要日期，您可以转换为 DATE 而不是 DATETIME。

虽然这不会处理时区。

如果您有不同时区的值，您可以使用:

UPDATE [your_table] SET my_date_time = DATE_ADD(CAST('2006-09-01T00:00:00-05:00' AS DATETIME), INTERVAL CAST(substr('2006-09-01T00:00:00-05:00',20,3) AS SIGNED) HOUR);

只有当时区的所有更改都是整小时(而不是半小时)时，这才有效。

希望对你有帮助