How to substract time from sysdate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to substract time from sysdate

  1. #1
    Join Date
    Apr 2002
    Posts
    61

    Question

    Hi,

    I need to substract a few seconds from the sysdate. I am using the sysdate in a 'where' clause. I know, I can substract days straight as follows
    select (sysdate-1) from dual.

    Please let me know your feedback.

    Thanks,
    Ramesh

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    If you consider that a day has 86400 seconds, you can for example subtract 3 seconds this way:

    (sysdate - 3/86400)

    Hope that helps.
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Apr 2002
    Posts
    61

    Smile Thanks

    Yep, you are right. Thanks David for the quick reply.

    Ramesh

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    David is right but how do you know that it subtracted that many seconds?

    Code:
    appdev@NICK>select (sysdate - 3/86400) "Secs?"
      2  from dual
      3  /
    
    Secs?
    ---------
    08-OCT-02
    You can see that it will have to be tried this way......

    Code:
    appdev@NICK>select (sysdate+1/(1*60*60*24)-sysdate-2/(1*60*60*24))*(60*60*24) "Secs!"
      2  from dual;
    
         Secs!
    ----------
            -1
    Cheers!

    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Mar 2002
    Posts
    534

    Talking

    Tarry,

    I didnt realy get the goal of your statment because your result will always be -1 whatever sysdate and the unit of sysdate is.

    sysdate+1/(1*60*60*24)-sysdate-2/(1*60*60*24))*(60*60*24)=
    ( (sysdate - sysdate) + (1-2) / (1*60*60*24)) * (60*60*24)=
    0 - 1 / (1*60*60*24) * (60*60*24)=
    -1 * (60*60*24) / (60*60*24)=
    -1

    to see the difference i would rather format the output with to_char(date_val,'DDMMYYYY HH24:MI:SS')

    Cheers
    Mike

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