All Forums Database
Mahs 32 posts Joined 04/11
17 Mar 2012
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.

ulrich 816 posts Joined 09/09
17 Mar 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Mahs 32 posts Joined 04/11
17 Mar 2012

Great Ulrich! it seems to be working. Thanks.

-Mahesh.

Mahs 32 posts Joined 04/11
17 Mar 2012

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) ?

ulrich 816 posts Joined 09/09
18 Mar 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.