Properly format postgres interval to HH:MM-CodePudding

Trying to calculate time interval in postgre,

SELECT 
    employee_id,
    clock_in,
    clock_out,
    (SELECT EXTRACT(epoch FROM (clock_out-clock_in))/3600) AS time_worked
FROM
    payroll_timelog
WHERE
    employee_id=31;

I am trying to format this to HH:MM format but I get base 10 floating points 7.975380470833334.

This should be 7.58 (7 hours 58 mins). This precision is important.

I tried casting to int:

(SELECT EXTRACT(epoch FROM (clock_out-clock_in))::int/3600) AS time_worked

but that rounds down to 7.

How can I make sure to get the desired result of 7.58?

CodePudding user response：

We can use TO_CHAR with HH24:MI format after subtracting from two datetime.

SELECT 
    employee_id,
    clock_in,
    clock_out,
    TO_CHAR(clock_out - clock_in,'HH24:MI')
FROM
    payroll_timelog
WHERE
    employee_id=31;

sqlfiddle