Search code examples
sqlpostgresqldatedatetimedate-arithmetic

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?

Solution

  • 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