Database Reference
In-Depth Information
6 'day' )
7 days_hours
8 from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
9 to_date('15-mar-2001 11:22:33','dd-mon-yyyy hh24:mi:ss') dt2
10 from dual )
11 /
YEARS_MONTHS DAYS_HOURS
--------------- ------------------------------
+000000001-00 +000000015 10:20:30.000000000
Now it is clear that there is 1 year, 15 days, 10 hours, 20 minutes, and 30 seconds between the two
DATE
s.
TIMESTAMP Type
The
TIMESTAMP
type is very much like the
DATE
, with the addition of support for fractional seconds and time zones.
We'll look at the
TIMESTAMP
type in the following three sections: one with regard to just the fractional second
support but no time zone support, and the other two with regard to the two methods of storing the
TIMESTAMP
with
time zone support.
TIMESTAMP
The syntax of the basic
TIMESTAMP
datatype is straightforward
TIMESTAMP(n)
where
N
is optional; it is used to specify the scale of the seconds component in the timestamp and may take on
values between 0 and 9. If you specify 0, then a
TIMESTAMP
is functionally equivalent to a
DATE
and, in fact, stores the
same values in the same manner:
EODA@ORA12CR1> create table t
2 ( dt date,
3 ts timestamp(0)
4 )
5 /
Table created.
EODA@ORA12CR1> insert into t values ( sysdate, systimestamp );
1 row created.
EODA@ORA12CR1> select dump(dt,10) dump, dump(ts,10) dump from t;
DUMP DUMP
----------------------------------- -----------------------------------
Typ=12 Len=7: 120,110,4,12,20,4,8 Typ=180 Len=7: 120,110,4,12,20,4,8
