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 aTIMESTAMP value whileCURRENT_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; |
|
<a name="tseu" "="">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 SYSDATECurrent_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; |