STR_TO_DATE和ISO8601 Atomtime格式 [英] STR_TO_DATE and ISO8601 Atomtime format

问题描述

我有一个MySQL数据库，我无法从中读取日期.问题是我有一个存储日期的varchar列.日期以原子时间格式存储，例如. 2014-06-01T00:00:00 + 02:00 .

I have a MySQL database, which I cannot alter, where I read date from. The issue is that I have a varchar column that stores a date. The date is stored in the atomtime format eg. 2014-06-01T00:00:00+02:00.

我无法弄清楚如何在STR_TO_DATE函数中指定格式.我尝试了 STR_TO_DATE(Endtime，'％Y-％m-％dT％H:％i:％s + 02:00')，但这不起作用.

I cannot figure how to specify the format in the STR_TO_DATE function. I tried STR_TO_DATE(Endtime, '%Y-%m-%dT%H:%i:%s+02:00'), but that doesn't work.

有人对此有解决方案吗?

Do anyone have a solution for this?

我正在尝试运行以下查询(无法正常运行):

I am trying to run the following query (which is not working properly):

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')

先谢谢了.

推荐答案

使用内置的日期时间算术，可以使用存储函数来解析时间戳并将时间戳从存储格式转换为MySQL的本机格式，这可能是最好的解决方法.函数进行时区转换.

This is probably best-addressed using a stored function to parse and convert the timestamps from the stored format into MySQL's native format, using the built-in date time math functions to do the time zone conversions.

下面的函数将正确处理两种格式， YYYY-MM-DDTHH:MM:SSZ 和 YYYY-MM-DDTHH:MM:SS +/- HH:MM 以及格式正确的MySQL日期时间文字，这些文字将通过未修改的形式传递.

The function below will correctly handle two formats, YYYY-MM-DDTHH:MM:SSZ and YYYY-MM-DDTHH:MM:SS+/-HH:MM as well as correctly formed MySQL datetime literals, which will be passed through unmodified.

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注入漏洞.

We assume that if the input data matches one of the patterns, then the contents of the value passed in are also going to be sane; if you give nonsense input values, you'll get some nonsense output, such as if you are using a time zone of '+99:00' but it won't fail. This function does not have any SQL injection vulnerabilities.

可以对代码进行进一步优化，但是按照编写的方式，此功能足够高效，可以在一台中等功率的计算机上每秒评估数千个表达式.

The code could be further-optimized, but as written, this function is efficient enough that it can evaluate several thousand expressions per second on a moderately-powered machine.

这篇关于STR_TO_DATE和ISO8601 Atomtime格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！