CODEWITHSUNDEEP
sqlpostgresql
Yankz Kuyateh
Yankz Kuyateh

Reputation: 743

Properly format postgres interval to HH:MM

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.

enter image description here

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?

Upvotes: 4

Views: 2020

Answers (2)

hadi ahadi
hadi ahadi

Reputation: 146

above answer ignores "day" and for "4 days 06:30:00" returns "06:30" that is not correct, to consider "day" should use this:

select concat(EXTRACT(DAY FROM (clock_out - clock_in)) * 24 + EXTRACT(HOUR FROM (clock_out - clock_in)),':',EXTRACT(MINUTE FROM (clock_out - clock_in))) AS time_worked

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 8

Related Questions