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

1 comment:

  1. Hello There. I discovered your weblog the use of msn. That is an extremely well written article.
    I'll make sure to bookmark it and come back to learn extra of your helpful info.
    Thank you for the post. I'll certainly return.

    ReplyDelete