| General Information |
| 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 RETURN VARCHAR2; |
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 '2009-03-15') FROM dual;
conn oe/oe
SELECT EXTRACT(hour FROM order_date) "Hour"
FROM orders; |
| |
| FROM_TZ |
| Converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value |
FROM_TZ(t IN TIMESTAMP_UNCONSTRAINED, timezone IN VARCHAR2)
RETURN TIMESTAMP_TZ_UNCONSTRAINED; |
SELECT FROM_TZ(TIMESTAMP '2010-11-20 08:00:00', '3:00')
FROM dual;
SELECT FROM_TZ(TIMESTAMP '2010-11-20 19:30:00', '3:00')
FROM dual; |
| |
| GREATEST |
| Return the Latest Timestamp |
GREATEST(pattern IN TIME_UNCONSTRAINED) RETURN TIME_UNCONSTRAINED;
GREATEST(pattern IN TIME_TZ_UNCONSTRAINED) RETURN TIME_TZ_UNCONSTRAINED;
GREATEST(pattern IN TIMESTAMP_UNCONSTRAINED) RETURN TIMESTAMP_UNCONSTRAINED;
GREATEST(pattern IN TIMESTAMP_TZ_UNCONSTRAINED) RETURN TIMESTAMP_TZ_UNCONSTRAINED;
GREATEST(pattern IN TIMESTAMP_LTZ_UNCONSTRAINED) RETURN TIMESTAMP_LTZ_UNCONSTRAINED; |
| TBD |
| |
| LEAST |
| Return the Earliest Timestamp |
LEAST(pattern IN TIME_UNCONSTRAINED) RETURN TIME_UNCONSTRAINED;
LEAST(pattern IN TIME_TZ_UNCONSTRAINED) RETURN TIME_TZ_UNCONSTRAINED;
LEAST(pattern IN TIMESTAMP_UNCONSTRAINED) RETURN TIMESTAMP_UNCONSTRAINED;
LEAST(pattern IN TIMESTAMP_TZ_UNCONSTRAINED) RETURN TIMESTAMP_TZ_UNCONSTRAINED;
LEAST(pattern IN TIMESTAMP_LTZ_UNCONSTRAINED) RETURN TIMESTAMP_LTZ_UNCONSTRAINED; |
| TBD |
| |
| 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 RETURN TIMESTAMP_UNCONSTRAINED; |
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 RETURN VARCHAR2; |
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_AT_TIMEZONE |
| Returns the Timestamp at the named timezone |
SYS_AT_TIME_ZONE(t time_tz_unconstrained, i VARCHAR2)
RETURN time_tz_unconstrained;
SYS_AT_TIME_ZONE(t timestamp_tz_unconstrained, i VARCHAR2)
RETURN timestamp_tz_unconstrained; |
SELECT tzname FROM v$timezone_names;
SELECT STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'US/Pacific')
FROM dual;
SELECT STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'Asia/Singapore')
FROM dual;
SELECT STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'Asia/Kuwait')
FROM dual; |
| |
| SYS_EXTRACT_UTC |
| Returns Coordinated Universal Time (UTC, formerly Greenwich Mean Time) from a Timestamp |
SYS_EXTRACT_UTC(t IN TIMESTAMP_TZ_UNCONSTRAINED)
RETURN TIMESTAMP_UNCONSTRAINED; |
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2004-03-28 11:30:00.00 -08:00')
FROM dual; |
| |
| SYSTIMESTAMP |
| Current Date Time as a Timestamp |
SYSTIMESTAMP RETURN TIMESTAMP_TZ_UNCONSTRAINED; |
SELECT SYSTIMESTAMP FROM dual;
SELECT SYSTIMESTAMP AT TIME ZONE 'Japan' 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(region IN VARCHAR2) RETURN VARCHAR2; |
| 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 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; |
| |
| 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 2010
SELECT timestamp'2011-02-29 00:00:00' + INTERVAL '1' year(1)
FROM dual;
SELECT timestamp'2011-02-28 00:00:00' + INTERVAL '1' year(1)
FROM dual;
SELECT add_months(timestamp'2012-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; |
