Database Reference
In-Depth Information
The datatypes are different (the
Typ=field
indicates that), but the manner in which they store data is identical.
The
TIMESTAMP
datatype will differ in length from the
DATE
type when you specify some number of fractional seconds
to preserve, for example:
EODA@ORA12CR1> create table t
2 ( dt date,
3 ts timestamp(9)
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
2 from t;
DUMP DUMP
----------------------------------- -----------------------------------
Typ=12 Len=7: 120,114,1,2,8,20,1 Typ=180 Len=11: 120,114,1,2,8,20,1,
53,55,172,40
Now the
TIMESTAMP
consumes 11 bytes of storage, and the extra 4 bytes at the end contain the fractional seconds,
which we can see by looking at the time that was stored:
EODA@ORA12CR1> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
EODA@ORA12CR1> select * from t;
DT TS
-------------------- ----------------------------------------------
02-jan-2014 07:19:00 02-JAN-14 07.19.00.892841000 AM
EODA@ORA12CR1> select dump(ts,16) dump from t;
DUMP
-----------------------------------
Typ=180 Len=11: 78,72,1,2,8,14,1,35,37,ac,28
EODA@ORA12CR1> select to_number('3537ac28', 'xxxxxxxx' ) from dual;
TO_NUMBER('3537AC28','XXXXXXXX')
--------------------------------
892841000
We can see the fractional seconds that were stored are there in the last 4 bytes. We used the
DUMP
function to
inspect the data in
HEX
this time (base 16) so we could easily convert the 4 bytes into the decimal representation.
