How to convert time to seconds in Oracle with Examples

In my previous articles i have given the idea about multiple functions in oracle sql. In this article i would like to throw light one of the important question asked – How to convert time to seconds in oracle with examples. The question to convert time hh:mi:ss to seconds is most important question and which is always useful in programming techniques.

Convert time hh:mi:ss to seconds in oracle :

In this section i would like to give multiple syntax as well as examples of how to convert time to seconds in oracle. These queries are useful in real programming. We require to convert time in seconds in most of the scenarions. In following examples i would like to explain the different scenarios to convert the hh:mi:ss to seconds.

Scenario 1 : Using System date and time to convert in seconds

In first scenario if we require to convert the system date in to seconds. Many times there is requirement to convert the system date and time in to seconds.

Example : How to convert the system date time to seconds.

To_char function is used to convert time to character.

Following query is useful.

SQL> select
  2    to_char(sysdate,'hh24:mi:ss') As "SystemTime",
  3    to_char(sysdate,'sssss') "Seconds"
  4  from dual;

SystemTime     Seconds
--------       -----
11:34:50       41750

As per the current time the to_char function has converted time in to seconds easily.

Scenario 2 : Using System date and time to convert in seconds using arithmetic equation

The second scenario is also the same but we require to use the arithmetic operations. One hour means 60 mins and 1 min means 60 seconds.So the following query is useful to convert time in to seconds.

SQL> select
  2    to_char(sysdate,'hh24:mi:ss') SystemTime,
  3    to_char(sysdate,'hh24')*60*60
  4      + to_char(sysdate,'mi')*60
  5      + to_char(sysdate,'ss') Seconds
  6  from
  7    dual;

SystemTime     Seconds
--------       -----
11:35:50       41810

If you see the query in detail the first to_char function will show the system time in hh:mi:ss format . The second to_char function will convert hours in to seconds and third to_char function can calculate minutes in seconds. We are adding all the seconds together so that we will get the correct seconds output.

Scenario 3 : Convert time to seconds with nls_session_parameters

I have explained the two types to convert the system time in to seconds. Hope you might know about the nls_session_parameters.

The first step is to select the NLS_TIMESTAMP_FORMAT and then next step is to change tat NLS_TIMESTAMP_FORMAT to ‘DD-MON-YYYY HH24:MI:SS’ .If you can observe query closedly with using round function we are fetching the hours and converting that hours in to seconds. Then we have used the extract function to fetch minutes and convert those in seconds with multiplying that with 60.At third stage we are extracting the seconds. We are adding all the seconds with using + arithmetic operator.

SQL> select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT';
VALUE
--------------------------------------------------------------------------------
DD-MON-RR HH.MI.SSXFF AM

SQL> alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> select
  2    to_char(sysdate,'hh24:mi:ss') time,
  3    round(extract(hour from cast(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss')as timestamp)))*60*60
  4      + extract(minute from current_timestamp)*60
  5      + extract(second from current_timestamp)) seconds
  6  from
  7    dual;

TIME        SECONDS
-------- ----------
15:24:54      55494

These are the most important examples where we can convert the hours to seconds or time to seconds. I hope you like this article. If you like this article or if you have any concerns with the same kindly comment in comments section.