Timestamp

Timestamp stores the century, four digits of a year, the month, the day, the hour in 24-hour format, the minute, the fractional second and a time zone. A timestamp has more attributes than a DATE: fractional second and time zone. There are three timestamp types:

Type	Description
TIMESTAMP[(seconds_precision)]	Stores the century, 4 digits of a year, month, day, hour in 24-hour format, minute, second, and optional precision for the seconds using seconds_precision. seconds_precision can be an integer from 0 to 9. The default is 9. 9 means to store up to 9 digits to the right of the decimal point for the second.
TIMESTAMP[(seconds_precision)] WITH TIME ZONE	add time zone to TIMESTAMP.
TIMESTAMP[(seconds_precision)] WITH LOCAL TIME ZONE	store datetime in the database local time zone.

The format used to specify a TIMESTAMP literal:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'


CREATE TABLE myTable(
   id            INTEGER,
   hiredate      TIMESTAMP(4)
); 
INSERT INTO myTable(id, hiredate) VALUES(1,TIMESTAMP '2012-05-13 07:15:31.1234' ); 

SQL> SELECT * FROM myTable;

        ID                      HIREDATE
------------------------------------------
         1    13-MAY-12 07.15.31.1234 AM


SQL>

Home »
Oracle »
Data types »

Timestamp: