<quote src=Expert Oracle Database Architecture>
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 and 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:
ops$tkyte@ORA10G> create table t
2 ( dt date,
3 ts timestamp(0)
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
1 row created.
ops$tkyte@ORA10G> select dump(dt,10) dump, dump(ts,10) dump
2 from t;
DUMP DUMP
------------------------------------ ------------------------------------
Typ=12 <b>Len=7</b>: 120,105,6,28,11,35,41 Typ=180 Len=7: 120,105,6,28,11,35,41
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:
ops$tkyte@ORA10G> create table t
2 ( dt date,
3 ts timestamp(9)
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t values ( sysdate, systimestamp );
1 row created.
ops$tkyte@ORA10G> select dump(dt,10) dump, dump(ts,10) dump
2 from t;
DUMP DUMP
------------------------------------- -------------------------------------
Typ=12 Len=7: 120,105,6,28,11,46,21 Typ=180 <b>Len=11:</b> 120,105,6,28,11,46,21
,44,101,192,208
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:
ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA10G> select * from t;
DT TS
-------------------- --------------------------------
28-jun-2005 10:45:20 28-JUN-05 10.45.20.744866000 AM
ops$tkyte@ORA10G> select dump(ts,16) dump from t;
DUMP
--------------------------------------------------
Typ=180 Len=11: 78,69,6,1c,b,2e,15,2c,65,c0,d0
ops$tkyte@ORA10G> select to_number('2c65c0d0','xxxxxxxx') from dual;
TO_NUMBER('2C65C0D0','XXXXXXXX')
--------------------------------
744866000
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.
....
TIMESTAMP WITH TIME ZONE Type
The TIMESTAMP WITH TIME ZONE type inherits all of the qualities of the TIMESTAMP type and adds time zone support. The TIMESTAMP WITH TIME ZONE type consumes 13 bytes of storage, with the extra two bytes being used to preserve the time zone information. It differs from a TIMESTAMP structurally only by the addition of these 2 bytes:
ops$tkyte@ORA10G> create table t
2 (
3 ts timestamp,
4 ts_tz timestamp with time zone
5 )
6 /
Table created.
ops$tkyte@ORA10G> insert into t ( ts, ts_tz )
2 values ( systimestamp, systimestamp );
1 row created.
ops$tkyte@ORA10G> select * from t;
TS TS_TZ
---------------------------- -----------------------------------
28-JUN-05 01.45.08.087627 PM 28-JUN-05 01.45.08.087627 PM -04:00
ops$tkyte@ORA10G> select dump(ts), dump(ts_tz) from t;
DUMP(TS)
-------------------------------------------------------------------------------
DUMP(TS_TZ)
-------------------------------------------------------------------------------
Typ=180 Len=11: 120,105,6,28,14,46,9,5,57,20,248
Typ=181 <b>Len=13:</b> 120,105,6,28,18,46,9,5,57,20,248,16,60
So, as you can see, upon retrieval the default TIMESTAMP format included the time zone information (I was on East Coast U.S. time during daylight saving time when this was executed).
TIMESTAMP WITH TIME ZONEs store the data in whatever time zone was specified when the data was stored. The time zone becomes part of the data itself. Note how the TIMESTAMP WITH TIME ZONE field stored ¿18,46,9¿ for the hour, minutes, and seconds (in excess-1 notation, so that is 17:45:08), whereas the TIMESTAMP field stored simply ¿14,46,9¿, which is 13:45:09¿the exact time in the string we inserted. The TIMESTAMP WITH TIME ZONE had four hours added to it, in order to store in GMT (also known as UTC) time. The trailing 2 bytes are used upon retrieval to properly adjust the TIMESTAMP value.
</quote>