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