Overview of Oracle Timestamp

The data datatype in Oracle in actually a date/time datatype. Next to the date a time in stored with an accuracy of seconds.

In Oracle 9i a new datatype called TIMESTAMP was introduced. This datatype contains also a date/time, but you can store a fractions of a second. Also a datatype TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE was introduces. This datatypes have smart logic about timezones.
You can specify a precision (0-9 digits) for TIMESTAMP. The default is 6 digits. The precision is the number of digits in the fractional part of the SECOND datetime field. For example TIMESTAMP(4) can contain 4 numbers behind the decimal seperator (example 0.3921 seconds).

Add substract intervals

You can now easy add and substract timestamp-intervals to dates and timestamps.
For example: Add 60 seconds: sysdate + INTERVAL '60' SECOND
Add 10 minutes: sysdate + INTERVAL '10' MINUTE
Add 1 hour: sysdate + INTERVAL '1' HOUR
Substract 1 day and 2 minutes: systimestamp - INTERVAL '1 00:02:00' DAY TO SECOND
Substract 2 months: sysdate - INTERVAL '2' MONTH

Sample code

For example insert into timestamp column of TABLE times(kol1 TIMESTAMP):

insert into times(kol1 ) values (timestamp'2010-01-10 06:30:00.00223344');
insert into times(kol1 ) values (localtimestamp);

TABLE times(kol1 TIMESTAMP WITH TIME ZONE)
INSERT INTO times(kol1) VALUES (timestamp'2010-01-10 09:00:00.00224455'); -- uses session timezone
insert into times(kol1) values ( timestamp'2010-01-10 10:23:00.333333 US/Eastern'); -- with timezone name
insert into times(kol1) values ( timestamp'2010-01-10 10:23:00.333333 EST'); -- with timezone abbrevation
insert into times(kol1 ) values (localtimestamp);
insert into times(kol1 ) values (systimestamp);
insert into times(kol1 ) values (current_timestamp);
insert into times(kol1 ) values (CAST(sysdate AS TIMESTAMP));

select to_timestamp ('10-Jan-2010 11:10:10.456632', 'DD-Mon-YYYY HH24:MI:SS.FF') from dual;

Determine values from timestamp

You can select a part of a timestamp with the EXTRACT function

SELECT
EXTRACT(year FROM kol1) year,
EXTRACT(month FROM kol1) month,
EXTRACT(day FROM kol1) dat,
EXTRACT(hour FROM kol1) hour,
EXTRACT(minute FROM kol1) minute,
EXTRACT(second FROM kol1) second,
EXTRACT(timezone_hour FROM kol1) tz_hour,
EXTRACT(timezone_minute FROM kol1) tz_minute,
EXTRACT(timezone_region FROM kol1) tz_reg,
EXTRACT(timezone_abbr FROM kol1) tz_abbr
FROM times;

-- select all timezones:
select *
from v$timezone_names;


SELECT systimestamp AT TIME ZONE dbtimezone FROM dual; 
FROM_TZ: Convert a timestamp to a different timezone.
SELECT FROM_TZ(TIMESTAMP '2010-01-10 09:10:00', '4:00')
FROM dual;

LOCALTIMESTAMP : Current date and time in the session time zone in a value of data type TIMESTAMP. 
CURRENT_TIMESTAMP: Current date and time in the session time zone in a value of data type TIMESTAMP WITH TIME ZONE.

Change timezones

-- database level
ALTER DATABASE SET time_zone = 'US/Eastern';
-- session level
ALTER SESSION SET time_zone = 'US/Eastern';
ALTER SESSION SET time_zone = local;
ALTER SESSION SET TIME_ZONE = '-5:0';

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT =
  'DD-Mon-YYYY HH24:MI:SSXFF TZR TZD';

Date formats can be found here.