Printable View
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
Date subtraction returns number of days. For example:
first, expand our default date format...
This is now...Code:SQL> alter session set nls_date_format="mm/dd/yyyy hh24:mi:ss";
Session altered.
This is now - 1 day...Code:SQL> select sysdate from dual;
SYSDATE
-------------------
07/03/2001 10:37:22
This is now - 1 hour (1/24th of a day)...Code:SQL> select sysdate - 1 from dual;
SYSDATE-1
-------------------
07/02/2001 10:37:28
This is now - 1 minute (1/(24hours * 60 minutes))...Code:SQL> select sysdate - 1/24 from dual;
SYSDATE-1/24
-------------------
07/03/2001 09:37:37
now - 1 second...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
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
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
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 ...
