如何使用MySQL获取ISO-8601(SCORM 2004)持续时间格式?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (407)

我有一个像列一样的sql列

PT2M52.37S
PT21.79S
PT53M29.68S
P
PT9S

有些MySQL函数可以将上述格式转换为秒?

PHP函数(它总是P,第一个字符)

function scorm_format_duration($duration) {
    // Fetch date/time strings.
    $stryears = get_string('years');
    $strmonths = get_string('nummonths');
    $strdays = get_string('days');
    $strhours = get_string('hours');
    $strminutes = get_string('minutes');
    $strseconds = get_string('seconds');

    if ($duration[0] == 'P') {
        // If timestamp starts with 'P' - it's a SCORM 2004 format
        // this regexp discards empty sections, takes Month/Minute ambiguity into consideration,
        // and outputs filled sections, discarding leading zeroes and any format literals
        // also saves the only zero before seconds decimals (if there are any) and discards decimals if they are zero.
        $pattern = array( '#([A-Z])0+Y#', '#([A-Z])0+M#', '#([A-Z])0+D#', '#P(|\d+Y)0*(\d+)M#',
                            '#0*(\d+)Y#', '#0*(\d+)D#', '#P#', '#([A-Z])0+H#', '#([A-Z])[0.]+S#',
                            '#\.0+S#', '#T(|\d+H)0*(\d+)M#', '#0*(\d+)H#', '#0+\.(\d+)S#',
                            '#0*([\d.]+)S#', '#T#' );
        $replace = array( '$1', '$1', '$1', '$1$2 '.$strmonths.' ', '$1 '.$stryears.' ', '$1 '.$strdays.' ',
                            '', '$1', '$1', 'S', '$1$2 '.$strminutes.' ', '$1 '.$strhours.' ',
                            '0.$1 '.$strseconds, '$1 '.$strseconds, '');
    } else {
        // Else we have SCORM 1.2 format there
        // first convert the timestamp to some SCORM 2004-like format for conveniency.
        $duration = preg_replace('#^(\d+):(\d+):([\d.]+)$#', 'T$1H$2M$3S', $duration);
        // Then convert in the same way as SCORM 2004.
        $pattern = array( '#T0+H#', '#([A-Z])0+M#', '#([A-Z])[0.]+S#', '#\.0+S#', '#0*(\d+)H#',
                            '#0*(\d+)M#', '#0+\.(\d+)S#', '#0*([\d.]+)S#', '#T#' );
        $replace = array( 'T', '$1', '$1', 'S', '$1 '.$strhours.' ', '$1 '.$strminutes.' ',
                            '0.$1 '.$strseconds, '$1 '.$strseconds, '' );
    }

    $result = preg_replace($pattern, $replace, $duration);

    return $result;
}
提问于
用户回答回答于

为了使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_timecmi.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

扫码关注云+社区

领取腾讯云代金券