Postgres converting double double precision to text creates '1.50000000000'

user_5975620 0
Viewed 576

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?

date-arithmetic date datetime postgresql sql
2 Answers

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
user_106767160

You may try first casting the quotient or modulus to integer, then casting to text after that, e.g.

SELECT (119 / 60)::int::text || 'h ' || (119 % 60)::int::text || 'm';

This outputs 1h 59m.

user_18632290
Related