Conversion: Seconds to MM:SS

Database
Enthusiast

Conversion: Seconds to MM:SS

Hello,

I have a  decimal(20,3) field storing data in seconds.

I want those seconds to be converted in to Minutes to seconds format.

For ex: 555 seconds should be shown as 9:15 seconds.

Please note that I cannot use  datatype INTERVAL MIUTE(4) TO SECOND since minutes could cross 4 digits here.

Please help.

-Mahesh.

4 REPLIES
Supporter

Re: Conversion: Seconds to MM:SS

So you mean that your result would be a string as it can not be expressed as Interval?

You need to calculate the seconds via the MOD function and can either substract these secs from the source column and divide by 60 to get the minutes.

something like thos

select (calendar_date - current_date)+0.15 as id, cast ((id - id mod 60)/60 as integer) !! ':' !! (id mod 60 (format'99.999'))
from sys_calendar.calendar
where id between 500 and 600
order by id;

Enthusiast

Re: Conversion: Seconds to MM:SS

Great Ulrich! it seems to be working. Thanks.

-Mahesh.

Enthusiast

Re: Conversion: Seconds to MM:SS

Btw, sort of simplified query i.e,,

select cast ( id/60 as integer) !! ':' !! (id mod 60 (format'99.999')) should give me same result. 

Did you have any reason to mention it like cast ((id - id mod 60)/60 as integer) instead of

 cast ( id/60 as integer) ?

Supporter

Re: Conversion: Seconds to MM:SS

It is OK as TD use only the integer part if you cast to int and does not round.

The other formular is independent from this rule and should be valid in all DB systems as id - id mod 60 is a natrual number which can be devided by 60.