Skip to content
Advertisement

Postgres converting double double precision to text creates ‘1.50000000000’

Working with postgres and I need to convert total minutes into the format of 1h 1m.

I’m doing this via the following code

replace(
    (119 / 60 + (119 % 60) / 100.0)::text, 
    '.', 
    'h '
) + 'm'

which returns 1h 59000000000000000000m

119 is just an example amount of minutes I’ve added, but this would be set on a larger query that would do

(accumulated_minutes / 60 + (accumulated_minutes % 60) / 100.0)::text, 

is there a way to convert this to text without adding the extra precision?

Advertisement

Answer

Let the database do the heavy lifiting for you! You can turn the number of minutes to an interval datatype, and use to_char() for formating:

to_char(make_interval(mins => accumulated_minutes), 'hh24"h" mi"m"')

Demo on DB Fiddle:

select to_char(make_interval(mins => 119), 'hh24"h" mi"m"') val
| val     |
| :------ |
| 01h 59m |

If you want to suppress the leading 0s on hours and minutes, then:

to_char(make_interval(mins => accumulated_minutes), 'fmhh24"h" fmmi"m"') val
Advertisement