STR_TO_DATE и ISO8601 в формате Atomtime

У меня есть база данных 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')

Заранее спасибо.

Источник

2 ответа

Решение

Это, вероятно, лучше всего решать, используя хранимую функцию для анализа и преобразования временных меток из сохраненного формата в собственный формат 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');

-- unexpected values will be cast as a datetime.

DECLARE offset_sign TINYINT DEFAULT NULL;
DECLARE offset_hours TINYINT DEFAULT NULL;
DECLARE offset_minutes TINYINT DEFAULT NULL;

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

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

  RETURN CONVERT_TZ(REPLACE(REPLACE(in_ts,'T',' '),'Z',''),'UTC',@@time_zone);

-- 2014-09-18T07:00-06:00 -- or +05:00

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

   SET offset_sign = IF(SUBSTRING(in_ts FROM 20 FOR 1) = '+', -1, +1); # we need to flip the sign, to "back out" the offset to get a time in UTC
   SET offset_hours = CAST(SUBSTRING(in_ts FROM 21 FOR 2) AS SIGNED) * offset_sign;
   SET offset_minutes = CAST(SUBSTRING(in_ts FROM 24 FOR 2) AS SIGNED) * offset_sign;
   RETURN CONVERT_TZ(DATE_ADD(DATE_ADD(REPLACE(SUBSTRING(in_ts FROM 1 FOR 19),'T',' '), INTERVAL offset_hours HOUR), INTERVAL offset_minutes MINUTE),'UTC',@@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-инъекций.

Код может быть дополнительно оптимизирован, но, как написано, эта функция достаточно эффективна, так что она может оценивать несколько тысяч выражений в секунду на компьютере с умеренной мощностью.

Источник

Вместо этого используйте 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');
Источник
Другие вопросы по тегам