时间戳

首页 > 解决方案 > 将中文本转换为日期

mysql - 将中文本转换为日期

问题描述

我有以 mediumtext 数据类型存储日期的列数据。如何将 mediumtext 转换为日期，以便提取特定时间范围内的数据。

谢谢

样本数据：

标签： mysqlsqldatetimestamp-with-timezone

解决方案

我们可以使用具有适当格式模型的 STR_TO_DATE 函数。该函数没有将时区缩写作为日期时间字符串的一部分来处理的格式模型，因此我们必须做一些字符串操作来解决它

例如

SELECT STR_TO_DATE( CONCAT(SUBSTRING_INDEX(t.foo,' ',4),' ',SUBSTRING_INDEX(t.foo,' ',-1)),'%a %b %d %T %Y') AS dt
     , SUBSTRING_INDEX(SUBSTRING_INDEX(t.foo,' ',5),' ',-1) AS tz
  FROM ( SELECT 'Fri Sep 11 15:30:55 JST 2015' AS foo
         UNION ALL SELECT 'Fri Sep 11 15:40:09 JST 2015'
         UNION ALL SELECT 'Fri Sep 11 14:55:09 SGT 2015'
         UNION ALL SELECT 'Fri Sep 11 14:57:38 SGT 2015'
         UNION ALL SELECT 'Fri Sep 11 03:59:02 EDT 2015'
         UNION ALL SELECT 'Fri Sep 11 01:10:17 PDT 2015'
       ) t

要处理时区，我们可以使用 CONVERT_TZ 函数，但需要填充 mysql.time_zone_name（和相关的 time_zone 表），以处理时区的值。

SELECT STR_TO_DATE( CONCAT(SUBSTRING_INDEX(t.foo,' ',4),' ',SUBSTRING_INDEX(t.foo,' ',-1)),'%a %b %d %T %Y') AS dt
     , SUBSTRING_INDEX(SUBSTRING_INDEX(t.foo,' ',5),' ',-1) AS tz

     , CONVERT_TZ( STR_TO_DATE( CONCAT(SUBSTRING_INDEX(t.foo,' ',4),' ',SUBSTRING_INDEX(t.foo,' ',-1)),'%a %b %d %T %Y')
                 , SUBSTRING_INDEX(SUBSTRING_INDEX(t.foo,' ',5),' ',-1)
                 , '+00:00'
       ) AS dt_utc
  FROM ( SELECT 'Fri Sep 11 01:10:17 PDT 2015' AS foo
         UNION ALL SELECT 'Fri Sep 11 01:10:17 PST8PDT 2015'
       ) t

返回

dt                   tz       dt_utc
-------------------  -------  -------------------
2015-09-11 01:10:17  PDT      (NULL)
2015-09-11 01:10:17  PST8PDT  2015-09-11 08:10:17

注意如果mysql.time_zone_name表不包含我们要转换的、或其他 tz 值的条目PDT，则该函数将返回 NULL。 EDTSGTCONVERT_TZ

跟进

在 WHERE 子句中使用这些类型的表达式将需要为表中的每一行计算表达式。给定我们正在查找的日期值范围，如果信息存储在 DATETIME 列中，并定义了适当的索引，则优化器可以使用范围扫描操作。

这使我们得出结论，就性能而言，MEDIUMTEXT 不是存储 DATETIME 值的最佳数据类型。

推荐阅读