Database Reference
In-Depth Information
Adding or Subtracting Time to/from a TIMESTAMP
The same techniques we applied to
DATE
for date arithmetic works with a
TIMESTAMP
, but
the
TIMESTAMP
will be
converted into a
DATE
in many cases using the preceding techniques
. For example:
EODA@ORA12CR1> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
EODA@ORA12CR1> select systimestamp ts, systimestamp+1 dt
2 from dual;
TS DT
----------------------------------- -----------------------------------
02-JAN-14 07.30.37.627678 AM -07:00 03-jan-2014 07:30:37
Note that adding 1 did, in fact, advance the
SYSTIMESTAMP
by a day, but the fractional seconds are gone, as would
be the time zone information. This is where using
INTERVAL
s will be more important:
EODA@ORA12CR1> select systimestamp ts, systimestamp +numtodsinterval(1,'day') dt
2 from dual;
TS DT
---------------------------------------- ----------------------------------------
02-JAN-14 07.31.45.451317 AM -07:00 03-JAN-14 07.31.45.451317000 AM -07:00
Using the function that returns an
INTERVAL
type preserved the fidelity of the
TIMESTAMP
. You will need to exercise
caution when using
TIMESTAMP
s to avoid the implicit conversions. But bear in mind the caveat about adding intervals
of months or years to a
TIMESTAMP
if the resulting day isn't a valid date—the operation fails (adding one month to the
last day in January will always fail if the month is added via an
INTERVAL
).
Getting the Difference Between Two TIMESTAMPs
This is where the
DATE
and
TIMESTAMP
types diverge significantly. Whereas the results of subtracting a
DATE
from a
DATE
was a
NUMBER
, the result of doing the same to a
TIMESTAMP
is an
INTERVAL
:
EODA@ORA12CR1> select dt2-dt1
2 from (select to_timestamp('29-feb-2000 01:02:03.122000',
3 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-mar-2001 11:22:33.000000',
5 'dd-mon-yyyy hh24:mi:ss.ff') dt2
6 from dual )
7 /
DT2-DT1
---------------------------------------------------------------------------
+000000380 10:20:29.878000000
