Converting time / interval to decimals in PostgreSQL

user_19276948 0
Viewed 50
CONCAT(
COALESCE(
FLOOR(MAX(work_duration)/60),0), 'h',
COALESCE(
MAX(work_duration) - (60*FLOOR(MAX(work_duration)/60))
,0),'mn') as "Work duration"

When executing the code, I get for example 1h30mn so I want to change it to be a decimal 1.5 I tried Cast ( "Work duration" as integer), but it is not working !

postgresql sql
2 Answers

Just divide the duration in minutes by 60 and possibly round to the desired precision:

work_duration::float/60 as "Work duration"

The ::float conversion is needed to avoid integer division.

In order to round the result to a given number of decimal place, use the ROUND function.

There is one technicality to observe: round is not defined on float but on the numeric data type, so there is the need to properly cast the arguments (all examples round to 3 decimal places):

round((1::float/60)::numeric, 3) // extending the answer above
round((1::numeric/60), 3)        // simplified

round((1::float/60), 3)          // fails !
user_5889160

You can use EXTRACT to get the EPOCH from your interval and from there you can calculate the hours and minutes as decimals, e.g.

SELECT EXTRACT(EPOCH FROM '1h30m'::interval)/60/60 AS "work duration";

   work duration    
--------------------
 1.5000000000000000
(1 row)
user_22753880
Related