Date Addition & Subtraction
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Date Addition & Subtraction

  1. #1
    Join Date
    Jun 2001
    Posts
    76
    I need to figure out how to do some basic date subtraction in oracle. Given one date "Tue, 03 Jun 2001 15:12:35", I need to subtract an arbitrary number of seconds.

    for example: The above date - 3600 would equal "Mon, 02 Jun 2001 15:12:35";

    What is the command for this? I have tried date_sub (), but that does not appear to work

    Thanks

    R
    On the other hand, you have different fingers.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Date subtraction returns number of days. For example:
    first, expand our default date format...
    Code:
    SQL> alter session set nls_date_format="mm/dd/yyyy hh24:mi:ss";
    
    Session altered.
    This is now...
    Code:
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    07/03/2001 10:37:22
    This is now - 1 day...
    Code:
    SQL> select sysdate - 1 from dual;
    
    SYSDATE-1
    -------------------
    07/02/2001 10:37:28
    This is now - 1 hour (1/24th of a day)...
    Code:
    SQL> select sysdate - 1/24 from dual;
    
    SYSDATE-1/24
    -------------------
    07/03/2001 09:37:37
    This is now - 1 minute (1/(24hours * 60 minutes))...
    Code:
    SQL> select sysdate, sysdate - 1/60/24 from dual;
    
    SYSDATE             SYSDATE-1/60/24
    ------------------- -------------------
    07/03/2001 10:37:58 07/03/2001 10:36:58
    now - 1 second...
    Code:
    SQL> select sysdate, sysdate - 1/60/60/24 from dual;
    
    SYSDATE             SYSDATE-1/60/60/24
    ------------------- -------------------
    07/03/2001 10:38:19 07/03/2001 10:38:18
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2001
    Posts
    76
    I'm afraid i was not clear. I was looking for something simpler (sans /24/60/60...).

    For instance, is there a comparable command to this (mySQL):

    select sysdate, date_sub(sysdate, INTERVAL 3 SECOND);
    SYSDATE date_sub(sysdate, INTERVAL 3 SECOND)
    ------------------- ------------------------------------
    07/03/2001 10:38:19 07/03/2001 10:38:16

    Thanks,
    R
    On the other hand, you have different fingers.

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    it's really clearer with Oracle, and there is no way to do it elsewhere ... why should it be since it's one of the simplest implementation of time calculation you can find ...
    nevertheless if you want you still can build a function which substracts the number of seconds given as an argument, but I wouldn't see any use of it ...

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