Timestamp

From Oracle FAQ
Jump to: navigation, search

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

Example[edit]

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[edit]

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[edit]

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 #