Get ISO-8601(SCORM 2004) Duration Format in Seconds using MySQL

Question

I have a sql column with values like

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

Is it possible with some MySQL Functions to convert the above format to seconds?

I have searched everything but didn't find something in exactly the same format as above. Any date mysql function I tried isn't working.

More information regarding this format: http://www.ostyn.com/standards/scorm/samples/ISOTimeForSCORM.htm

PHP Function (it always will be P, the first char)

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;
}

Answer 1

To make the STR_TO_DATE works, you need to add %f for the Microseconds.

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