使用MySQL获取ISO-8601(SCORM2004)持续时间格式(以秒为单位
使用MySQL获取ISO-8601(SCORM2004)持续时间格式(以秒为单位
提问于 2015-10-16 21:53:27
我有一个sql列，其值如下所示
PT2M52.37S
PT21.79S
PT53M29.68S
P
PT9S是否可以使用某些MySQL函数将上述格式转换为秒？
我已经搜索了所有内容，但没有找到与上面格式完全相同的内容。我尝试过的任何日期mysql函数都不起作用。
有关此格式的更多信息，请访问：http://www.ostyn.com/standards/scorm/samples/ISOTimeForSCORM.htm
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;
}回答 3
Stack Overflow用户
发布于 2018-05-30 06:51:38
要使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 dbPT27M59.08S => 00:27:59.080000
PT2H27M37.11S => 02:27:37.110000
P4DT19H46M18.22S => 115:46:18.220000
Stack Overflow用户
发布于 2016-09-06 21:58:01
我已经创建了两个函数来将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;
ENDStack Overflow用户
发布于 2015-10-16 22:08:26
我不认为有一个标准的函数可以做到这一点。使用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支持这种格式。更好的是，他们有非常好的documentation来解释它。
编辑：
您可以使用这个杂乱无章的工具更轻松地处理所有格式。它在每个组件后插入一个空格，然后使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33172258
复制相关文章
相似问题