DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: oracle date in seconds

  1. #1
    Join Date
    Jul 2009
    Posts
    1

    oracle date in seconds

    Hi,
    the date in oracle is stored in seconds (like 1246067125) and i'm unable to convert it into calender date and i also need converting calender date into seconds (like 1246067125)
    i tried some thing like
    select to_date('01/01/1970','DD/MM/YYYY') + trunc(creation_date/86400) from table; but i'm not sure about it

    BTW - how to insert calender date into oracle, where date is stored in seconds..

    Thanks in advance
    ram

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by ramdaya View Post
    the date in oracle is stored in seconds
    Since Oracle stores DATE datatype in seven bytes - Century, Year, Month, Day, Hour, Minutes, Seconds - I assume you are saying somebody designed a database where for some unknown reason decided to convert dates to seconds then store that value in a NUMBER or so datatype, is that correct?

    If that's the case that person most probably created a set of functions to deal with that particular design feature -look at functions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2008
    Posts
    45

    Hey,

    why will anyone want to store the date in secs?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by chaitanyagoa View Post
    why will anyone want to store the date in secs?
    I don't know and, I don't want to know
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Not that hard

    Date to seconds to date is not that complex:
    Code:
    SQL> SET ECHO ON VER OFF
    SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
    
    Session altered.
    
    SQL> COL sec new_value sec
    SQL> SELECT SYSDATE, TO_CHAR (SYSDATE, 'J') * 86400
      2                + TO_CHAR (SYSDATE, 'SSSSS') sec
      3    FROM DUAL;
    
    SYSDATE                       SEC
    ------------------- -------------
    2009-08-05 12:53:05  212116279985
    
    SQL> SELECT &&sec sec,
      2           TO_DATE (TRUNC (&&sec / 86400), 'J')
      3        + ((&&sec - TRUNC (&&sec / 86400) * 86400) / 86400)
      4    FROM DUAL;
    
              SEC TO_DATE(TRUNC(21211
    ------------- -------------------
     212116279985 2009-08-05 12:53:05
    
    SQL>
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width