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.
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'))
where id between 500 and 600
order by id;
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) ?
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.