PostgreSQL - how to format number into custom time(hours, minutes)-CodePudding

i have problem to figure out how to select column as custom time format. I have column let say "time" with row value "70" and i want to format it to

1h 10min

and when the value will be in example "50" then i need such output:

50min

i've read about formating like HH:MI:SS but i want to include that "h" for hours and "min" for minutes.

CodePudding user response：

The simplest way is converting to the interval or using divide. Something like this (two options 1 custom format):

    with t as (select 70 as minutes
               union
               select 210
               union
               select 50
               union
               select 0
               union
               select 10000)
    select
        t.minutes,
        (t.minutes || 'minutes')::interval as full_time,
        make_interval(mins => t.minutes) as full_time2,
        t.minutes/60 || 'h ' || mod(t.minutes,60) || 'min' as custom,
case when t.minutes < 60 then '' else t.minutes/60 || 'h ' end || mod(t.minutes,60) || 'min' as custom2
    from t
    order by t.minutes;