Convert seconds (integer) to time

I have a column in a table that I'm working with on Pervasive SQL, which stores the time elapsed from midnight in seconds. 86400 is the maximum value for this column (24 hours). I'm struggling with converting this into a time format, namely in hh:mm:ss.

I've used CONVERT with DATEADD (convert(dateadd(second, 0, [time_field]), SQL_TIME, 108)) and CONVERT with division/modulus to get each hour, minute and second field to no avail. For instance, if a value in [time_field] was 34837, the output should be 09:40:37. However, the first approach just returns 00:00:00 for all rows. The second approach, which uses the query below, returns values like 65:00:54 when the input value is 51714.

right('00'+CONVERT(updateid_updtime/3600, SQL_VARCHAR), 2) + ':' +
right('00'+CONVERT(mod(updateid_updtime, 3600)/60, SQL_VARCHAR), 2) + ':' +
right('00'+CONVERT(mod(updateid_updtime, 60), SQL_VARCHAR), 2)

Converting it into VARCHAR outputs a timestamp value (not desired) like 2041-08-03 00:00:00.

These queries seem to work in MS SQL, but I guess the issue could be the start of the time in PSQL which start from 01-01-0001? Any suggestions would be greatly appreciated.

Assuming the column is called seconds and it stores integers, try this:

select
      seconds / 3600 hh
    , (seconds / 60) % 60 mm
    , seconds % 60 ss
from your_table

This should calculate the hours, minutes and seconds.

From these it should be possible to concatenate the wanted format

select
    concat(right('00' || seconds / 3600,2),':'
         , right('00' || (seconds / 60) % 60,2),':'
         , right('00' || seconds % 60,2)
          ) hh_mm_ss
from your_table

nb: done without access to pervaseive sql, so untested in that envronment. Refer: https://docs.actian.com/ingres/10s/index.html#page/SQLRef/String_Functions.htm

The following example using postgres:

select
    seconds
  , seconds / 3600 hh
  , (seconds / 60) % 60 mm
  , seconds % 60 ss
  , concat(right('00' || seconds / 3600,2),':'
         , right('00' || (seconds / 60) % 60,2),':'
         , right('00' || seconds % 60,2)
         ) hh_mm_ss
from your_table

fiddle