Convert Minutes to Hours in PostgreSQL

Question

Currently having Minutes(bigint) as

I want to convert this Minutes to hours. Is there any function to convert it?

Eg: 390 as 6.30

Why not just simply divide it with 60? Like SELECT minutes::numeric / 60 — pozs
– pozs, Commented Aug 8, 2014 at 13:37

Community · Accepted Answer · 2017-05-23 12:05:01Z

8

Something like this should work:

SELECT TO_CHAR('390 minute'::interval, 'HH24:MI')...

From an answer to a related question: PostgreSQL - How to convert seconds in a numeric field to HH:MM:SS

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Aug 8, 2014 at 12:36

davek

23k11 gold badges80 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Newbie Over a year ago

I dont want to convert to char.. Just want to convert my minutes(bigint) to hours(numeric(18,2))

Doon Over a year ago

if you want numeric wouldn't 390, be 6.5?

Doon · Accepted Answer · 2014-08-08 15:37:54Z

You should be able to cast them to an interval and then use date_part to pull out the required fields

 select date_part('hours',interval '1 minute' * minutes) as hours, date_part('minutes',interval '1 minute' * minutes) as minutes;

example:

dev=# select date_part('hours',interval '1 minute' * 390) as hours, date_part('minutes',interval '1 minute' * 390) as minutes; hours | minutes -------+--------- 6 | 30

edit: in response to other comment..

select date_part('hours',interval '1 minute' * minutes) + (date_part('minutes',interval '1 minute' * minutes) * .01);

to force it to a numeric.

example:

select date_part('hours',interval '1 minute' * 390) + (date_part('minutes',interval '1 minute' * 390) * .01) as foo; foo ----- 6.3 (1 row)

cast to numeric

select cast(date_part('hours',interval '1 minute' * 390) * 1.0 + (date_part('minutes',interval '1 minute' * 390) * .01) as numeric(4,2)) as foo; foo ------ 6.30 (1 row)

or

dev=# select cast(date_part('hours',interval '1 minute' * 240) * 1.0 + (date_part('minutes',interval '1 minute' * 240) * .01) as numeric(4,1)) as foo; foo ----- 4.0 (1 row)

Vivek S. · Accepted Answer · 2014-10-18 11:14:42Z

you can have a PostgreSQL Function for that,

CREATE OR REPLACE FUNCTION fn_min_to_hrs(mins int) RETURNS numeric AS $BODY$ select cast(date_part('hours',interval '1 minute' * mins) * 1.0 + (date_part('minutes',interval '1 minute' * mins) * .01) as numeric(18,2)); $BODY$ LANGUAGE sql VOLATILE

select fn_min_to_hrs(390) Result +-------------+ |fn_min_to_hrs| |numeric(18,2)| +-------------+ | 6.30 | +-------------+

Vasilii Suricov · Accepted Answer · 2023-08-09 22:17:46Z

Not sure if it's even useful for anybody to get 6.30 instead 6.5 but anuway

select concat_ws('.', (390/60)::text, (390%60)::text)

to get 6.30 as text

select (390/60.0)

to get 6.5 as float

Collectives™ on Stack Overflow

Convert Minutes to Hours in PostgreSQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Related