Database Reference
In-Depth Information
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:
EODA@ORA12CR1> select numtoyminterval
2 (trunc(months_between(dt2,dt1)),'month')
3 years_months,
4 dt2-add_months(dt1,trunc(months_between(dt2,dt1)))
5 days_hours
6 from (select to_timestamp('29-feb-2000 01:02:03.122000',
7 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
8 to_timestamp('15-mar-2001 11:22:33.000000',
9 'dd-mon-yyyy hh24:mi:ss.ff') dt2
10 from dual )
11 /
YEARS_MONTHS DAYS_HOURS
------------- -----------------------------
+000000001-00 +000000015 10:20:30.000000000
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:
EODA@ORA12CR1> select numtoyminterval
2 (trunc(months_between(dt2,dt1)),'month')
3 years_months,
4 dt2-(dt1 + numtoyminterval( trunc(months_between(dt2,dt1)),'month' ))
5 days_hours
6 from (select to_timestamp('29-feb-2000 01:02:03.122000',
7 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
8 to_timestamp('15-mar-2001 11:22:33.000000',
9 'dd-mon-yyyy hh24:mi:ss.ff') dt2
10 from dual )
11 /
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.
