Converting time / interval to decimals in PostgreSQL-CodePudding

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 !

CodePudding user response：

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 !

CodePudding user response：

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)