Convert epoch duration (few days) in ms to time-CodePudding

I am trying to convert duration in ms to human-readable format. It works fine if it is below 24 hours, but once it crosses that value it starts again from 0. Is it possible to keep the hours once it crosses the day limit?

My current code implementation (in Postgres) contains following:

create table test (time_unix text);

insert into test values ('7200000'); --2h
insert into test values ('28800000'); --8h
insert into test values ('1800000'); -- 30min
insert into test values ('252000000'); -- 70h
insert into test values ('86400000') -- 24h

select (timestamp 'epoch'   time_unix::bigint* interval '1 ms')::time
from test;

current output:

time
02:00:00
08:00:00
00:30:00
22:00:00
00:00:00

desired output:

time
02:00:00
08:00:00
00:30:00
70:00:00
24:00:00

CodePudding user response：

As a_horse_with_no_name commented, the data type time represents a "point in time" rather than a duration. So 70h can not be represented as a time value (the largest time value is 24:00:00). You probably want to convert it to an interval instead:

SELECT time_unix::bigint * INTERVAL '1 ms' FROM test;

 ?column? 
══════════
 02:00:00
 08:00:00
 00:30:00
 70:00:00
 24:00:00
(5 rows)

The data type text is utterly inappropriate to store numbers; please use something more sane.