Timestamp

Timestamp is an extension of the Date datatype that can store date and time data (including fractional seconds).

Example

SQL> CREATE TABLE t1(c1 TIMESTAMP);
Table created.
 
SQL> INSERT INTO t1 VALUES (SYSTIMESTAMP);
1 row created.

SQL> SELECT * FROM t1;
C1
---------------------------
10-DEC-07 10.32.47.797201 AM

SQL> SELECT TO_CHAR(c1, 'DD/MON/YYYY HH24:MI:SS') FROM t1;
TO_CHAR(C1,'DD/MON/Y
--------------------
10/DEC/2007 10:32:47

Internal storage

The timestamp type takes 11 bytes of storage:

SQL> SELECT dump(c1) FROM t1;
DUMP(C1)
----------------------------------------------------
Typ=180 Len=11: 120,107,12,19,23,32,48,21,164,131,24

The internal format is the following one:

1. century + 100
2. year in the century + 100
3. month
4. day
5. hour + 1
6. minute + 1
7. second + 1
8. the next 4 bytes gives the nanoseconds in the second

In case of a TIMESTAMP WITH TIME ZONE, 2 more bytes give the time zone, hour and minutes with a value between -12 and +14 for the hour part and between 0 and 59 for the minute part. The format of these two bytes is the following one:

1. hour part of time zone + 20
2. minute part of the time zone + 60

SQL> ALTER TABLE t1 ADD (c2 TIMESTAMP WITH TIME ZONE);

Table altered.

SQL> UPDATE t1 SET c2=SYSTIMESTAMP;

1 row updated.

SQL> SELECT dump(c2) FROM t1;
DUMP(C2)
----------------------------------------------------------------
Typ=181 Len=13: 120,109,9,21,13,32,18,19,64,150,192,22,60

With a time zone of +2:00.

Also see

• Date
• TIMESTAMP WITH TIME ZONE

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#