How to convert seconds in time format in SQL

In my previous article i have given the basic idea about how to convert time to seconds. In this article i would like to give you the idea about how to convert in to time format in SQL with multiple examples.There are so many times we have requirement to convert hours or seconds to the time format in the programming scenarios. I would like to explain multiple scenarios to time format. We have so many formats of time and there are so many requirements to time formats.

Oracle: Convert the seconds to time – hh:mi:ss

In this section i would like to give you multiple ways to time with examples. There are so many functions which we can use in to the specified time format. I would like to give multiple scenarios in to time format which is hh:mi:ss

Scenario 1 : Seconds to time format using numtodsinterval and to char function.

SQL> select
  2    to_char(
  3          trunc(sysdate)
  4          + numtodsinterval(55494, 'second')
  5      ,'hh24:mi:ss'
  6    ) As "Current_Time"
  7  from dual;

Current Time
--------
13:24:33

If you can see the query closely the 3 key functions we are using to convert the seconds in to time format.

1.to_char function : This is used to change the time format in to character.

2.trunc : The trunc function is used to truncate the seconds in to hours: Minutes format.

3.numtodsinterval : This is important function which is used to convert the seconds in to hh:mm format.

Scenario 2 : Seconds to time format using simple to_char and to_date function

The second scenario is quite simple where we are using to_char and to_date function for converting the seconds to time format. You can add any of the time format.

SQL> select
  2    to_char(
  3        to_date(55494,'sssss')
  4    ,'hh24:mi:ss') As "Current_Time"
  5  from dual;

Current_Time
--------
12:24:54

The above example is used to convert in to the correct time format. Here we are using only 2 functions –

1. To_char : The function is used to convert the time in to character format.
2. To_date : To convert the seconds in to specified date format.

Scenario 3 : Using Arithmetic and logical operations in to time format

The third scenario is straightforward way to convert the seconds in to time format.With using simple to_char and trunc and mod functions together we can convert the seconds in to time format. The following query is useful in to time format.

SQL> select
  2      to_char(trunc(46854/3600),'fm9900') || ':' ||
  3      to_char(trunc(mod(46854,3600)/60),'fm00') || ':' ||
  4      to_char(mod(46854,60),'fm00') "Current_Time"
  5  from dual;

Current_Time
-------------
13:24:54

Here we have used to_char and trunc,mod functions together to convert the seconds in to the specified time format.