| 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; |
