How to substract time from sysdate

[rkonda99]

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

[dknight]

If you consider that a day has 86400 seconds, you can for example subtract 3 seconds this way:

(sysdate - 3/86400)

Hope that helps.

[rkonda99]

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

Ramesh

[Tarry]

David is right but how do you know that it subtracted that many seconds?

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......

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!

[mike9]

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