Получите формат продолжительности ISO-8601 (SCORM 2004) в секундах, используя MySQL

Чтобы STR_TO_DATE работало, вам нужно добавить %f для микросекунд.

SELECT duration,
CASE
    WHEN duration LIKE 'P%DT%H%M%.%S' THEN STR_TO_DATE(duration, 'P%dDT%HH%iM%s.%fS')
    WHEN duration LIKE 'P%DT%H%M%S' THEN STR_TO_DATE(duration, 'P%dDT%HH%iM%sS')
    WHEN duration LIKE 'PT%H%M%.%S' THEN STR_TO_DATE(duration, 'PT%HH%iM%s.%fS')
    WHEN duration LIKE 'PT%H%M%S' THEN STR_TO_DATE(duration, 'PT%HH%iM%sS')
    WHEN duration LIKE 'PT%M%.%S' THEN STR_TO_DATE(duration, 'PT%iM%s.%fS')
    WHEN duration LIKE 'PT%M%S' THEN STR_TO_DATE(duration, 'PT%iM%sS')
    WHEN duration LIKE 'PT%.%S' THEN STR_TO_DATE(duration, 'PT%s.%fS')
    WHEN duration LIKE 'PT%S' THEN STR_TO_DATE(duration, 'PT%sS')
END as session
FROM db

PT27M59.08S => 00:27:59.080000

PT2H27M37.11S => 02:27:37.110000

P4DT19H46M18.22S => 115:46:18.220000

Я создал 2 функции для преобразования результата cmi.session_time и cmi.total_time из ISO8601 в секунды:

CREATE FUNCTION 'ISO8601_duration_delete_miliseconds'('isoDuration' VARCHAR(512)) RETURNS varchar(512) CHARSET latin1
BEGIN
  DECLARE strIndex INT;
  DECLARE strRes varchar(512);

  SET strIndex = INSTR(isoDuration, '.');
  if (strIndex > 0) then
    set strRes = substring(isoDuration, 1, strIndex-1);
    set strRes = concat(strRes, 'S');
  else
    set strRes = isoDuration;
  end if;

  RETURN strRes;
END

И последняя функция:

CREATE FUNCTION `iso8601duration_to_seconds`(`isoDuration` VARCHAR(512)) RETURNS int(11)
BEGIN
  DECLARE strIndex INT;
  DECLARE strRes varchar(512);
  DECLARE intRes INT;

  set strRes =  ISO8601_duration_delete_miliseconds(isoDuration);

  if (INSTR(isoDuration, 'H') > 0) then
    set intRes = time_to_sec(str_to_date(strRes, 'PT%HH%iM%sS'));
  elseif (INSTR(isoDuration, 'M') > 0) then
    set intRes = time_to_sec(str_to_date(strRes, 'PT%iM%sS'));
  else
    set intRes = time_to_sec(str_to_date(strRes, 'PT%sS'));
  end if;

  RETURN intRes;
END

Я не думаю, что есть стандартная функция для этого. Использование str_to_date() возможно, но оно не будет работать из-за отсутствующих компонентов времени. Если у вас есть только минуты и секунды, вы можете сделать это болезненно со строковыми операциями.

select ((case when p like '%M%S'
              then substring_index(substring_index(p, 'M', 2), 'M', -1)
              when p like '%T%S'
              then substring_index(substring_index(p, 'T', 2), 'T', -1)
              else ''
         end) +
        (case when p like 'T%M'
              then substring_index(substring_index(p, 'T', 2), 'T', -1) + 0
              else 0
         end) * 60
        ) as seconds

Это не очень легко обобщить, поскольку добавляется больше компонентов времени. Компонент секунд, например, является вторым элементом, когда разделителем является «M» или «T». Иногда вам просто нужно задаться вопросом, о чем думает комитет ISO, когда они создают эти стандарты.

Обратите внимание, что MySQL преобразует строку в числа, используя начальные цифры (если они есть). Вышеупомянутое использует эту функцию.

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

Также отмечу, что SAS поддерживает этот формат. Более того, у них действительно хорошие документация, объясняющая это.

РЕДАКТИРОВАТЬ:

С этим кладжем вам будет легче работать со всеми форматами. Он вставляет пробел после каждого компонента, а затем использует substring_index():

select (case when newp like '%S'
             then substring_index(substring_index(newp, 'S', 1), ' ', -1) + 1
             else 0
        end) as seconds,
       (case when newp like '%M%'
             then substring_index(substring_index(newp, 'M', 1), ' ', -1) + 1
             else 0
        end) as minutes,
       . . .
from (select replace(replace(replace(replace(replace(p, 'T', 'T '
                                                    ), 'Y', 'Y '
                                            ), 'D', 'D '
                                    ), 'H', 'H '
                            ), 'S', 'S '
                    ) as newp
      from t
     ) t