how to convert the time which is in hh:mm:ss to just mm:ss

[seshu]

Hi every body to morning my cousine has show his application and also his live db in that to sav the length of all the voice files he has saved in time format ie he took the datatype of time now i want to generate a an excell sheet of that table but here i want the time to be like mm:ss but not hh:mm:ss examle

the length of song sweet dreams is 01:12:35 now i want this to be shown as
72:35 how is there any procedure to convert directly in mysql or should i have to use a frontend code

regards
seshu

[ronverdonk]

You can convert the hour part of the time string to minutes, add the minutes to it and concatenate with the seconds from the time string, as follows:

SELECT CONCAT(
              SUBSTR(mytime,1,2)*60+SUBSTR(mytime,4,2),
              ':',
              SUBSTR(mytime,7,2)
              ) 
       AS TIME time_in_mins from test;

the result is a char string.

Ronald :cool: