CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Timestamps
Version 11.1
General
List of Time Zones set linesize 121
col tzname format a30
col tzabbrev format a30

SELECT *
FROM gv$timezone_names;
 
CURRENT_TIMESTAMP
Current Timestamp CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP FROM DUAL;
 
DBTIMEZONE

Current Time Zone
DBTIMEZONE
ALTER SESSION SET time_zone = local;

SELECT DBTIMEZONE FROM DUAL;

SELECT CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT DBTIMEZONE FROM DUAL;

SELECT CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET time_zone = local;

SELECT SYSTIMESTAMP FROM DUAL;

SELECT SYSTIMESTAMP AT TIME ZONE dbtimezone FROM DUAL;
 
DUMP
Returns the number of bytes and datatype of a value DUMP(<value>)
SELECT DUMP(SYSTIMESTAMP) FROM DUAL;
 
EXTRACT

Extracts and returns the value of a specified datetime field from a datetime or interval value expression
Values That Can Be Extracted:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

EXTRACT (<type> FROM <datetime | interval>)
SELECT EXTRACT(YEAR FROM DATE '2007-04-01') FROM DUAL;
 
FROM_TZ
Converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value FROM_TZ(<timestamp> <timestamp>)
SELECT FROM_TZ(TIMESTAMP '2007-11-20 08:00:00', '3:00')
FROM DUAL;

SELECT FROM_TZ(TIMESTAMP '2007-11-20 19:30:00', '3:00')
FROM DUAL;
 
LOCALTIMESTAMP

Current date and time in the session time zone in a value of data type TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value
LOCALTIMESTAMP
ALTER SESSION SET TIME_ZONE = '-5:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-8:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

-- The following statement fails because the mask does not include
-- the TIME ZONE portion of the return type of the function:

INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

-- The following statement uses the correct format mask
-- to match the return type of LOCALTIMESTAMP:

INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

SELECT * FROM local_test;
 
SESSIONTIMEZONE
Returns the value of the current session's time zone SESSIONTIMEZONE
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = '-5:00';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

ALTER SESSION SET time_zone = local;
 
SYS_EXTRACT_UTC
Returns Coordinated Universal Time (UTC, formerly Greenwich Mean Time) from a Timestamp SYS_EXTRACT_UTC(<date_time with timezone)
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2004-03-28 11:30:00.00 -08:00')
FROM DUAL;
 
SYSTIMESTAMP
Current Date Time as a Timestamp SYSTIMESTAMP
SELECT SYSTIMESTAMP FROM DUAL;
 
TRUNC
Returns the date only TRUNC(<value>)
SELECT TO_CHAR(SYSTIMESTAMP) FROM DUAL;

SELECT TO_CHAR(TRUNC(SYSTIMESTAMP)) FROM DUAL;
 
TZ_OFFSET
Returns the Time Zone Offset TZ_OFFSET(<time_zone>)
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
 
Timestamp Data Type Demos

Timestamp (Without Time Zone)
CREATE TABLE ts_test (
x TIMESTAMP,
y TIMESTAMP(0),
z TIMESTAMP(9));

desc ts_test

INSERT INTO ts_test
(x, y, z)
VALUES
(timestamp'2007-08-08 09:00:00.123456789',
timestamp'2007-08-08 09:00:00.123456789',
timestamp'2007-08-08 09:00:00.123456789');

set linesize 121
col x format a30
col y format a21
col z format a31

SELECT * FROM ts_test;

INSERT INTO ts_test
(x, y, z)
VALUES
(LOCALTIMESTAMP, localtimestamp, localtimestamp);

SELECT * FROM ts_test;

SELECT VSIZE(x), VSIZE(y), VSIZE(z)
FROM ts_test;

Table With Time Zone
CREATE TABLE tswtz_test (
msg VARCHAR2(40),
x   TIMESTAMP WITH TIME ZONE);

desc tswtz_test

ALTER SESSION SET TIME_ZONE = '-6:00';

col x foramt a35

INSERT INTO tswtz_test
(msg, x)
VALUES
('literal TS without TZ', timestamp'2004-08-08 09:00:00.123456789');

INSERT INTO tswtz_test
(msg, x)
VALUES
('SysTimeStamp (has TZ from DB)', SYSTIMESTAMP);

INSERT INTO tswtz_test
(msg, x)
VALUES
('LocalTimeStamp (has NO TZ)', LocalTimeStamp);

INSERT INTO tswtz_test
(msg, x)
VALUES
('CURRENT_TIMESTAMP (has TZ from client)', Current_Timestamp);

SELECT * FROM tswtz_test;

/* Lastly, notice the behavior of CURRENT_DATE and SYSDATE Current_Date (new with 9i) is a lot like SYSDATE but is timezone sensitive. */

SELECT TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROM DUAL;

ALTER SESSION SET time_zone = local;

SELECT TO_CHAR(CURRENT_DATE, 'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(sysdate, 'dd-mon-yyyy hh24:mi:ss')
FROM DUAL;

Extract Timestamp Components
col TR format a10

SELECT
EXTRACT(year FROM SYSTIMESTAMP) EY,
EXTRACT(month FROM SYSTIMESTAMP) EM,
EXTRACT(day FROM SYSTIMESTAMP) ED,
EXTRACT(hour FROM SYSTIMESTAMP) EH,
EXTRACT(minute FROM SYSTIMESTAMP) EM,
EXTRACT(second FROM SYSTIMESTAMP) ES,
EXTRACT(timezone_hour FROM SYSTIMESTAMP) TH,
EXTRACT(timezone_minute FROM SYSTIMESTAMP) TM,
EXTRACT(timezone_region FROM SYSTIMESTAMP) TR,
EXTRACT(timezone_abbr FROM SYSTIMESTAMP) TA
FROM DUAL;

Extract Current Timestamp Components
col TR format a10

SELECT
EXTRACT(year FROM CURRENT_TIMESTAMP) EY,
EXTRACT(month FROM CURRENT_TIMESTAMP) EM,
EXTRACT(day FROM CURRENT_TIMESTAMP) ED,
EXTRACT(hour FROM CURRENT_TIMESTAMP) EH,
EXTRACT(minute FROM CURRENT_TIMESTAMP) EM,
EXTRACT(second FROM CURRENT_TIMESTAMP) ES,
EXTRACT(timezone_hour FROM CURRENT_TIMESTAMP) TH,
EXTRACT(timezone_minute FROM CURRENT_TIMESTAMP) TM,
EXTRACT(timezone_region FROM CURRENT_TIMESTAMP) TR,
EXTRACT(timezone_abbr FROM CURRENT_TIMESTAMP) TA
FROM DUAL;

Extract Current Timestamp Components after altering the time zone
col TR format a10

ALTER SESSION SET time_zone = 'US/Eastern';

SELECT
EXTRACT(year FROM CURRENT_TIMESTAMP) EY,
EXTRACT(month FROM CURRENT_TIMESTAMP) EM,
EXTRACT(day FROM CURRENT_TIMESTAMP) ED,
EXTRACT(hour FROM CURRENT_TIMESTAMP) EH,
EXTRACT(minute FROM CURRENT_TIMESTAMP) EM,
EXTRACT(second FROM CURRENT_TIMESTAMP) ES,
EXTRACT(timezone_hour FROM CURRENT_TIMESTAMP) TH,
EXTRACT(timezone_minute FROM CURRENT_TIMESTAMP) TM,
EXTRACT(timezone_region FROM CURRENT_TIMESTAMP) TR,
EXTRACT(timezone_abbr FROM CURRENT_TIMESTAMP ) TA
FROM DUAL;

set serveroutput on

DECLARE
 t TIMESTAMP WITH TIME ZONE:=timestamp'2005-01-01 01:01:01 US/Pacific';
BEGIN
  dbms_output.put_line(extract(timezone_abbr from t));
  dbms_output.put_line(extract(timezone_region from t));
END;
/

ALTER SESSION SET time_zone = local;
 
Interval

Interval Demo
CREATE TABLE tint_test (
msg        VARCHAR2(25),
start_date TIMESTAMP WITH TIME ZONE,
end_date   TIMESTAMP WITH TIME ZONE,
duration_1 INTERVAL DAY(5) TO SECOND,
duration_2 INTERVAL YEAR TO MONTH);

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my plane ride',
timestamp'2004-08-08 17:02:32.212 US/Eastern',
timestamp'2004-08-08 19:10:12.235 US/Pacific');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my vacation',
timestamp'2004-07-27 06:00:00',
timestamp'2004-08-04 18:00:00');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my life', timestamp'1950-01-15 02:00:00', CURRENT_TIMESTAMP);

SELECT * FROM tint_test;

UPDATE tint_test
SET duration_1 = (end_date - start_date) DAY(5) TO SECOND,
duration_2 = (end_date - start_date) YEAR TO MONTH;

SELECT msg, duration_1, duration_2 FROM tint_test;

SELECT t.*, end_date - start_date FROM tint_test t;
 
Time Math

Time Math Demo
SELECT CURRENT_TIMESTAMP + INTERVAL '5' year(1)
FROM DUAL;

SELECT CURRENT_TIMESTAMP + INTERVAL '10:30' MINUTE TO SECOND
FROM DUAL;

-- this will fail ... there is no Feb. 29th in 2005
SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1)
FROM DUAL;

SELECT timestamp'2004-02-28 00:00:00' + INTERVAL '1' year(1)
FROM DUAL;

SELECT ADD_MONTHS(timestamp'2004-02-29 00:00:00',12)
FROM DUAL;

-- math with intervals
SELECT a.duration_1 + b.duration_1 + c.duration_1
FROM tint_test a, tint_test b ,tint_test c
WHERE a.msg = 'my plane ride'
AND b.msg LIKE '%vacat%'
AND c.msg like '%life';

-- but not aggregations
SELECT SUM(duration_1)
FROM tint_test;
 
Related Topics
Conversion Functions
Date Functions
Miscellaneous Functions
Numeric Functions
String Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [327 users online]    © 2010 psoug.org