how to update stored hh:mm value to hh:mm:ss in mysql database, the column datatype is String?-CodePudding

i have stored data in the database with String datatype as 10:56, 10:30:89

like this, now is it possible to convert all the data with hh:mm:ss format i.e. 10:30:89

Please help me here

CodePudding user response：

If your database is holding on a varchar column data related to time in two different formats hh:mm and hh:mm:ss and you want to unify all the records to have the same format you need to identify the records in hh:mm and add a ':00' to them at the end.

You can do that with a query similar to that one:

UPDATE MYTABLE
SET MYCOLUMN = CONCAT(MYCOLUMN, ':00')
WHERE MYCOLUMN NOT LIKE '%:%:%'