Oracle Timestamp Data Types
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
 
Time Zones
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 '2021-03-15') FROM dual;

conn oe/oe@pdbdev

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 '2021-03-15 08:00:00', '3:00')
FROM dual;

SELECT FROM_TZ(TIMESTAMP '2021-03-15 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 '2021-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'2021-08-08 09:00:00.123456789',
timestamp'2021-08-08 09:00:00.123456789',
timestamp'2021-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'2021-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'2021-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;

CURRENT_TIMESTAMP+INTERVAL'5'YEAR(1)
---------------------------------------
31-OCT-26 01.39.13.537205000 PM +00:00

SELECT current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;

CURRENT_TIMESTAMP+INTERVAL'10:30'MINUTETOSECOND
------------------------------------------------
31-OCT-21 08.50.13.601000000 AM -05:00

-- this will fail ... there is no Feb. 29th in 2021
SELECT timestamp'2021-02-29 00:00:00' + INTERVAL '1' year(1)
FROM dual;
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SELECT timestamp'2021-02-28 00:00:00' + INTERVAL '1' year(1)
FROM dual;

TIMESTAMP'2021-02-2800:00:00'+INTERVAL'1'YEAR(1)
-------------------------------------------------
28-FEB-22 12.00.00.000000000 AM

SELECT add_months(timestamp'2021-02-28 00:00:00',12)
FROM dual;

ADD_MONTHS(TIMESTAMP
---------------------
28-FEB-2022 00:00:00

Related Topics
Built-in Functions
Built-in Packages
CAST
Conversion Functions
Date Functions
DBMS_SCHED_MAIN_EXPORT.TS_TO_CHAR
Interval Data Types
Miscellaneous Functions
Numeric Functions
String Functions
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx