Calculate the only necessary difference and Group by between two Timestamps in PostgreSQL-CodePudding

I see similar question but didn't find helpful answer for my problem. I have one query:

    select au.username
        ,ej.id as "job_id"
        ,max(ec."timestamp") as "last_commit"
        ,min(ec."timestamp") as "first_commit"
        ,age(max(ec."timestamp"), min(ec."timestamp")) as "diff as age"
        ,to_char(age(max(ec."timestamp")::timestamp, min(ec."timestamp")::timestamp),'HH:MI:SS') as "diff as char"
        ,et.id as "task_id"
from table and etc..

And that my output(Sorry for picture but its best view):

So, as you can see I have timestamp with zones, and I trying calculate difference between last_commit and first_commit. Within function age its goes well, but I need extract only hours and minutes from this subtraction. N.B! only hours and minutes not days for example job_id=1 first row, the difference is 2 minutes and 42 seconds and where job_id=2 second row, the difference is 2 hours and 2 minutes and 55 sec, not 16 days X 24 hours, I don't need calculate days. When I try to_char its return not exactly what I expect. Last two columns within green color in my picture, show what I expect and want. So for every row calculate difference between last and first commit included only hours and minutes (in other words calculate only time not dates) and calculate total sum by task_id as represent in last column in pic.
Thanks.

CodePudding user response：

try this :

SELECT age(max(ec."timestamp"), min(ec."timestamp")) - date_trunc('day', age(max(ec."timestamp"), min(ec."timestamp")))

CodePudding user response：

You can try converting a Timestamp type to just time, like in this answer.

The result of string SQL is:

select au.username
        ,ej.id as "job_id"
        ,max(ec."timestamp") as "last_commit"
        ,min(ec."timestamp") as "first_commit"
        ,(max(ec."timestamp")::time-min(ec."timestamp")::time) as "diff as age"
        ,to_char(age(max(ec."timestamp")::timestamp, min(ec."timestamp")::timestamp),'HH:MI:SS') as "diff as char"
        ,et.id as "task_id"

There are otres possible solutions working with timestamp, but this one i consider simple.