How to get minutes using SQL in PostgreSQL

Question

I have a result using SQL inside a function

select a[2] as hour, a[3] minutes from ( select regexp_split_to_array('for  7h 18m', ' ') ) as dt(a);

and i got result as

hour    minutes
7h       18m

I want to get

minutes
 438

ChrisB · Accepted Answer · 2016-10-21 08:00:52Z

4

The documentation has an example using extract and epoch.

SELECT EXTRACT(EPOCH FROM '2h 18m'::INTERVAL)/60;

answered Oct 21, 2016 at 8:00

ChrisB

2,6152 gold badges28 silver badges45 bronze badges

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

1 Comment

deadpool Over a year ago

This is also awesome...Thanks

Tim Biegeleisen · Accepted Answer · 2016-10-21 08:05:22Z

2

One option here is to remove the h from the hours string, multiply by 60 to get minutes, then add this quantity to the minutes field, with the m removed. Try this query:

select regexp_replace(a[2], '[h\s]', '')::integer * 60 +
       regexp_replace(a[3], '[m\s]', '')::integer as minutes
from
(
    select regexp_split_to_array('for  7h 18m', ' ')
) as dt(a);

edited Oct 21, 2016 at 8:05

answered Oct 21, 2016 at 7:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

deadpool Over a year ago

getting error Error in query: ERROR: invalid input syntax for integer: ""

Collectives™ on Stack Overflow

How to get minutes using SQL in PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related