mysql iso 时间_使用MySQL获取ISO-8601(SCORM 2004)持续时间格式(以秒为单位)

我有一个sql列,其值为

PT2M52.37S

PT21.79S

PT53M29.68S

P

PT9S

有些MySQL函数可以将上面的格式转换为秒吗？

我搜索过所有内容,但没有找到与上述格式完全相同的内容.

我试过的任何日期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

标签：mysql,sql,datetime,iso8601

来源： https://codeday.me/bug/20190628/1314613.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！