SQLite Incomplete Time String

I have a table with incomplete time strings, such as ‘0:09.735’ (minutes:seconds.milliseconds).

As the function strftime expects certain formats, such as ‘%H:%M:%f’, I have “to complete” the incomplete timestring first.

SELECT
  strftime('%M:%f', '00:00:09.735', '+2.5 seconds') AS Duration1, -- works correctly
  strftime('%M:%f', '0:00:09.735',  '+2.5 seconds') AS Duration2, -- shows NULL
  strftime('%M:%f', '0:09.735',     '+2.5 seconds') AS Duration3, -- shows NULL
  strftime('%M:%f', '9.735',        '+2.5 seconds') AS Duration4  -- shows incorrect result

The first strftime function with the complete timestring shows the correct result, the second, third, and fourth one with the incomplete timestring do not.

Is there a flexible way that can convert all forms of incomplete timestrings into complete ones?

Answer

Assuming that the dot and the milliseconds are always there, you can use string functions to manipulate the times:

SELECT strftime(
         '%M:%f',
         SUBSTR('00:00:00', 1, 9 - INSTR(timestring, '.')) || timestring, 
         '+2.5 seconds'
       ) result
FROM tablename

Replace timestring with the name of the column.

But it would be better to update the column:

UPDATE tablename
SET timestring = SUBSTR('00:00:00', 1, 9 - INSTR(timestring, '.')) || timestring

so you have valid time values for SQLite.

See the demo.