Oracle: convert time hh:mi:ss to seconds



Example 1: Convert time to seconds
SQL> select
  2    to_char(sysdate,'hh24:mi:ss') time,
  3    to_char(sysdate,'sssss') seconds
  4  from dual;

TIME     SECON
-------- -----
15:24:54 55494



Example 2: Convert time to seconds
SQL> select
  2    to_char(sysdate,'hh24:mi:ss') time,
  3    to_char(sysdate,'hh24')*60*60
  4      + to_char(sysdate,'mi')*60
  5      + to_char(sysdate,'ss') seconds
  6  from
  7    dual;

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



Example 3: to convert time to seconds
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




Related Posts
- Why to Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
- Oracle: Convert seconds to time - hh:mi:ss
- Oracle: Some Important Date Queries
- Playing With Truncate and Date
- Oracle: Dates Difference in days, hours, minutes & seconds

No comments:

Post a Comment