www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Timestamp datatype

Database/Sql

Oracle Regular Expressions
Timestamp
SQL Date format
String concatenation
Loop in pl/sql
SQL IN-clause
Regular Expressions Examples
Flashback query
Grant/revoke privileges
Sequence
Rename tables, columns
Insert into Oracle
Database name
Table with sequenced numbers
Oracle connect by
Add columns to table


  OraDev.com

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.