Chapter 12 ■ Datatypes
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:
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.
EODA@ORA12CR1> create table t
3 ts timestamp,
4 ts_tz timestamp with time zone
EODA@ORA12CR1> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
1 row created.
Upon retrieval, the default TIMESTAMP WITH TIME ZONE format included the time zone information (I was on U.S. Mountain Standard Time when this was executed).
TIMESTAMP WITH TIME ZONEs store the data in whatever time zone was specified when the data was stored.