Adding or Subtracting Time to/from a TIMESTAMP-Datatypes
The same techniques we applied to DATE for date arithmetic work with a TIMESTAMP, but the TIMESTAMP will be converted into a DATE in many cases using the preceding techniques. For example:
SQL> alter session set nls_date_format = ‘dd-mon-yyyy hh24:mi:ss’; Session altered.
SQL> select systimestamp ts, systimestamp+1 dt 2 from dual;TS DT
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 INTERVALs will be more important:
SQL> select systimestamp ts, systimestamp +numtodsinterval(1,’day’) dt from dual;TS DT
Using the function that returns an INTERVAL type preserved the fidelity of the TIMESTAMP. You will need to exercise caution when using TIMESTAMPs 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 result of subtracting a DATE from a DATE was a NUMBER, the result of doing the same to a TIMESTAMP is an INTERVAL:
$ sqlplus eoda/foo@PBD1
SQL> select dt2-dt1from (select to_timestamp(’29-feb-2000 01:02:03.122000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt1,to_timestamp(’15-mar-2001 11:22:33.000000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt2from dual );DT2-DT1
The difference between two TIMESTAMP values is an INTERVAL, and this shows us the number of days and hours/minutes/seconds between the two. If we desire to have the years, months, and so forth, we are back to using a query similar to the one we used with dates:
SQL> select numtoyminterval(trunc(months_between(dt2,dt1)),’month’)years_months,dt2-add_months(dt1,trunc(months_between(dt2,dt1)))days_hoursfrom (select to_timestamp(’29-feb-2000 01:02:03.122000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt1,to_timestamp(’15-mar-2001 11:22:33.000000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt2from dual );YEARS_MONTHS DAYS_HOURS
Note in this case, since we used ADD_MONTHS, DT1 was converted implicitly into a DATE type, and we lost the fractional seconds. We would have to add yet more code to preserve them. We could use NUMTOYMINTERVAL to add the months and preserve the TIMESTAMP; however, we would be subject to runtime errors:
SQL> select numtoyminterval(trunc(months_between(dt2,dt1)),’month’)years_months,dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),’month’ ))days_hoursfrom (select to_timestamp(’29-feb-2000 01:02:03.122000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt1,to_timestamp(’15-mar-2001 11:22:33.000000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt2from dual );dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),’month’ ))
ERROR at line 4:
ORA-01839: date not valid for month specified
I personally find this unacceptable. The fact is, though, that by the time you are displaying information with years and months, the fidelity of the TIMESTAMP is destroyed already. A year is not fixed in duration (it may be 365 or 366 days in length) and neither is a month. If you are displaying information with years and months, the loss of microseconds is not relevant; having the information displayed down to the second is more than sufficient at that point.