Oracle Find time from Seconds past midnight

Question

In my Oracle table, I have a column with Number of Seconds since midnight (like 75135) and I want to identify its human readable time format. Do we have any command in Oracle ? please help me on this

Regards, Manohar

Answer 1

That is an interval:

select interval '1' second * 75135 from dual;

Answer 2

You can use:

SELECT TO_CHAR(
         TRUNC(SYSDATE) + 71370 * INTERVAL '1' SECOND,
         'YYYY-MM-DD HH24:MI:SS'
       ) AS seconds_past_midnight
FROM   DUAL;

or

SELECT TO_CHAR(
         TRUNC(SYSDATE) + 71370 / 86400,
         'YYYY-MM-DD HH24:MI:SS'
       ) AS seconds_past_midnight
FROM   DUAL;

Which, outputs:

SECONDS_PAST_MIDNIGHT
2022-06-07 19:49:30

If you just want the time component then:

SELECT TO_CHAR(
         TRUNC(SYSDATE) + 71370 * INTERVAL '1' SECOND,
         'HH24:MI:SS'
       ) AS seconds_past_midnight
FROM   DUAL;

Which, outputs:

SECONDS_PAST_MIDNIGHT
19:49:30

db<>fiddle here

Answer 3

Oracle provides the SSSSS format element that enables you to get the number of seconds past midnight from a given datetime value.

Example:

SELECT   TO_CHAR(TIMESTAMP '2022-06-07 00:01:05', 'SSSSS') as seconds_past_midnight
FROM DUAL;

SECONDS_PAST_MIDNIGHT
00065