STR_TO_DATE和ISO8601 Atomtime格式(STR_TO_DATE and ISO

我有一个MySQL数据库，我不能改变，在那里我阅读的日期。问题是，我有一个存储日期varchar列。日期存储在atomtime格式如。 2014-06-01T00:00:00+02:00 。

我想不出如何指定在STR_TO_DATE函数的格式。我试图STR_TO_DATE(Endtime, '%Y-%m-%dT%H:%i:%s+02:00')但不工作。

不要任何人有一个解决方案？

我试图运行下面的查询（不正常）：

SELECT *, COUNT(*) as antal 
 FROM ivocall_calls
WHERE Agentname LIKE 'Vinh Nguyen'
  AND Status1 = 'SALG'
  AND STR_TO_DATE(Endtime, '%Y-%m-%dT%H:%i:%s+02:00') 
        BETWEEN STR_TO_DATE('2014-06-01T00:00:00+02:00', '%Y-%m-%dT%H:%i:%s+02:00') 
            AND STR_TO_DATE('2014-06-30T00:00:00+02:00', '%Y-%m-%dT%H:%i:%s+02:00')

提前致谢。

Answer 1:

这可能是最好的解决使用存储功能来分析和转换从存储格式的时间戳到MySQL的本机格式，使用内置的日期时间的数学函数做时区转换。

该功能将在下文正确处理两种格式， YYYY-MM-DDTHH:MM:SSZ和YYYY-MM-DDTHH:MM:SS+/-HH:MM以及正确形成的MySQL日期时间文字，这将通过未修饰的传递。

DELIMITER $$

DROP FUNCTION IF EXISTS `from_iso8601_subset` $$
CREATE FUNCTION `from_iso8601_subset`(in_ts TINYTEXT) RETURNS DATETIME
DETERMINISTIC
NO SQL
BEGIN

-- this function takes an input timestamp value in a suppported subset of iso8601 values, and
-- and converts it to the equivalent MySQL datetime value, expressed in the current session's
-- time zone.  Since this is also the timezone that columns in the TIMESTAMP data type expect,
-- this causes the input value to be stored correctly in the native TIMESTAMP format, which is.
-- UTC under the hood.

-- if you are taking the value here and stuffing it into a DATETIME column, you need to have your
-- session @@time_zone set to the same zone in which that column should be stored, or use
-- CONVERT(from_iso('input value'),'UTC','Your Desired Time Zone');

-- 2014-02-01T23:59:59Z --

IF (in_ts REGEXP '^[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}[T ][[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}(Z|[+-][[:digit:]]{2}:[[:digit:]]{2})$') THEN

  SET in_ts = REPLACE(REPLACE(in_ts, 'T', ' '), 'Z', '+00:00');
  RETURN CONVERT_TZ(SUBSTRING(in_ts FROM 1 FOR 19), SUBSTRING(in_ts FROM 20 FOR 24), @@time_zone);

-- unexpected format -- let MySQL's built-in functions do the best they can; this will throw warnings
-- if the input is not a yyyy-mm-dd hh:mm:ss datetime literal; alternately this could return NULL.

ELSE

  RETURN CAST(in_ts AS DATETIME);

END IF;

END $$

DELIMITER ;

示例输出：

mysql> SET @@time_zone = 'America/New_York';
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT from_iso8601_subset('2014-06-01T00:00:00+02:00');
+--------------------------------------------------+
| from_iso8601_subset('2014-06-01T00:00:00+02:00') |
+--------------------------------------------------+
| 2014-05-31 18:00:00                              |
+--------------------------------------------------+
1 row in set (0.08 sec)

mysql> set @@time_zone = 'UTC';
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT from_iso8601_subset('2014-06-01T00:00:00+02:00');
+--------------------------------------------------+
| from_iso8601_subset('2014-06-01T00:00:00+02:00') |
+--------------------------------------------------+
| 2014-05-31 22:00:00                              |
+--------------------------------------------------+
1 row in set (0.08 sec)

我们假设，如果输入的数据相匹配的模式之一，然后传递在价值的内容也将是明智的; 如果你给废话输入值，你会得到一些废话输出，如如果你使用的时区“+99：00”，但它不会失败。此功能没有任何SQL注入漏洞。

该代码可以进一步优化，但写的，这个功能是足够的效率，它可以适度供电的机器上评估每秒几千表达式。

Answer 2:

使用UNIX_TIMESTAMP（）代替：

SELECT something, COUNT() as antal FROM ivocall_calls 
WHERE Agentname LIKE 'Vinh Nguyen' 
AND Status1 = 'SALG' 
AND unix_timestamp(Endtime) BETWEEN 
    unix_timestamp('2014-06-01T00:00:00+02:00' and unix_timestamp('2014-06-30T00:00:00+02:00');