Computed worked hours from SQL query

Hi.

I’ve collected worked hours from my database and the result is a time interval data type.
It’s visualized as ‘12 days, 23:25:20’ but it could be displayed as ‘311:25:20’.

I’m using PostgreSQL as backend and I’ve written following function:

create or replace function to_hours(interval) returns interval as
$$
select (trunc(extract( epoch from $1 )::numeric/3600::numeric,0) || ' hours')::interval +
           (extract( minutes from $1) || ' minutes')::interval +
           (extract( secs from $1) || ' secs')::interval +
           '00:00:00'::interval
$$ language SQL;

It works:

test=> select to_hours('12 days, 23:25:20'::interval);
  to_hours
-----------
 311:25:20
(1 row)

but if I use this function in my redash query, redash still display ‘12 days, 23:25:20’.

Have you hints to keep the hours kind of display for the time interval data type?

Hello nonsolosft, and welcome to the community!

Did you try to create a new table visualisation and edit the date column type? image

Select Number or text may do the trick

Thank you edouardjmn,

I’ve fixed using a cast into SQL query to text.

2 Likes