{"id":582,"date":"2017-08-08T15:59:52","date_gmt":"2017-08-08T08:59:52","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=582"},"modified":"2025-05-29T23:56:53","modified_gmt":"2025-05-30T06:56:53","slug":"oracle-timestamp-with-time-zone","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-timestamp-with-time-zone\/","title":{"rendered":"Oracle TIMESTAMP WITH TIME ZONE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Oracle TIMESTAMP WITH TIME ZONE<\/code> data type to store date and time data that includes time zones.<\/p>\n\n\n\n

Introduction to Oracle TIMESTAMP WITH TIME ZONE #<\/a><\/h2>\n\n\n\n

The TIMESTAMP WITH TIME ZONE<\/code> data type stores both the time stamp and time zone data.<\/p>\n\n\n\n

The time zone data can be time zone offset e.g., -07:00<\/code> which is a difference between local time and UTC time, or time zone region name e.g.,Europe\/London<\/code><\/p>\n\n\n\n

The following expression specifies the TIMESTAMP WITH TIME ZONE<\/code> data type:<\/p>\n\n\n

TIMESTAMP [(fractional_seconds_precision)] WITH<\/span> TIME<\/span> ZONE   <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This expression, fractional_seconds_precision<\/code> is an optional argument that specifies the number of digits in the fractional part of the SECOND<\/code> field.<\/p>\n\n\n\n
The fractional_seconds_precision<\/code> ranges from 0 to 9. Its default value is 6.<\/p>\n\n\n\n
Oracle considers two TIMESTAMP WITH TIME ZONE<\/code> values to be equal if they represent the same value in UTC regardless of the time zone data.<\/p>\n\n\n\n
For example, the following TIMESTAMP WITH TIME ZONE<\/code> values are equal:<\/p>\n\n\n
TIMESTAMP '2017-08-09 07:00:00 -7:00'\nTIMESTAMP '2017-08-09 09:00:00 -5:00'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement proves this:<\/p>\n\n\n
SELECT<\/span>\n  DECODE<\/span>( TIMESTAMP<\/span> '1999-01-15 8:00:00 -8:00'<\/span>, \n          TIMESTAMP<\/span> '1999-01-15 11:00:00 -5:00'<\/span>, \n          'Equal'<\/span>, \n          'Not Equal'<\/span> )\nFROM<\/span>\n  dual;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Noted that the DECODE()<\/code> function returns the third argument if the first second equals the second argument, otherwise, it returns the fourth argument.<\/p>\n\n\n\n
It is a good practice to use the TIMESTAMP WITH TIME ZONE<\/code> data type for the application that stores across time zones e.g., event scheduling, banking, and booking applications, etc.<\/p>\n\n\n\n
TIMESTAMP WITH TIME ZONE literals #<\/a><\/h2>\n\n\n\n
The following format specifies a TIMESTAMP WITH TIME ZONE<\/code> literal:<\/p>\n\n\n
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
TIMESTAMP '2017-08-10 10:30:20.15 -07:00'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Besides the time zone offset, you can use the time zone region name as follows:<\/p>\n\n\n
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZR'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
TIMESTAMP '2017-08-10 10:30:20.15 US\/Pacific'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For some regions that have Daylight Saving Time and Standard Time, you should include the TZD<\/code> format element explicitly to avoid the ambiguity of boundary when the time switches from one to another.<\/p>\n\n\n\n
For example, PST<\/code> for US\/Pacific<\/code> standard time and PDT<\/code> for US\/Pacific<\/code> daylight saving time. The following value ensures that the Daylight Saving Time value is returned:<\/p>\n\n\n
TIMESTAMP '2017-08-10 10:30:20.15 US\/Pacific PDT'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t specify PDT<\/code>, Oracle interprets it as the Standard Time if the ERROR_ON_OVERLAP_TIME<\/code> session parameter is set to FALSE by default or issues an error if ERROR_ON_OVERLAP_TIME<\/code> is set to TRUE.<\/p>\n\n\n\n
The default format for TIMESTAMP WITH TIME ZONE value #<\/a><\/h2>\n\n\n\n
By default, the date format for TIMESTAMP WITH TIME ZONE<\/code> data type is controlled by the NLS_TIMESTAMP_TZ_FORMAT<\/code> parameter.<\/p>\n\n\n
SELECT<\/span>\n  value<\/span>\nFROM<\/span>\n  V$NLS_PARAMETERS\nWHERE<\/span>\n  parameter = 'NLS_TIMESTAMP_TZ_FORMAT'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The default value in the Oracle Database is:<\/p>\n\n\n
DD-MON-RR HH.MI.SSXFF AM TZR<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to change this default format, you use the ALTER SESSION SET<\/code> statement.<\/p>\n\n\n
ALTER<\/span> SESSION<\/span> SET<\/span> NLS_TIMESTAMP_TZ_FORMAT = 'format'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Oracle TIMESTAMP WITH TIME ZONE data type example #<\/a><\/h2>\n\n\n\n
Step 1. Create a new table calledlogs<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> logs<\/span> (\n  log_id NUMBER<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n  log_message VARCHAR2<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n  created_at TIMESTAMP<\/span>\n  WITH<\/span>\n    TIME<\/span> ZONE NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (log_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The data type of the created_at<\/code> column is TIMESTAMP WITH TIME ZONE<\/code>. Next, we will show you various ways to insert data into this column.<\/p>\n\n\n\n
SELECT<\/span> SESSIONTIMEZONE FROM<\/span> dual;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Currently, it is set to:<\/p>\n\n\n
-07:00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  logs<\/span> (log_message, created_at)\nVALUES<\/span>\n  (\n    'Insert a timestamp'<\/span>,\n    TIMESTAMP<\/span> '2017-08-08 2:00:00'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the time stamp value does not have time zone data, Oracle appended the session time zone before storing it.<\/p>\n\n\n\n
Step 4. Retrieve data from the logs<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> logs<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Oracle<\/figure>\n\n\n\n
Step 5. Insert a date time in character string format into the created_at<\/code> column<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  logs<\/span> (log_message, created_at)\nVALUES<\/span>\n  (\n    'Insert a timestamp with timezone as a character string'<\/span>,\n    '08-AUG-2017 2:00:00 PM -05:00'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, Oracle converted the character string to the corresponding TIMESTAMP WITH TIME ZONE<\/code> value.<\/p>\n\n\n\n
Step 6. Retrieve the inserted value from the logs<\/code> table:<\/p>\n\n\n\n
\"Oracle<\/figure>\n\n\n\n
Step 7. insert a TIMESTAMP WITH TIME ZONE<\/code> literal into the created_at<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  logs<\/span> (log_message, created_at)\nVALUES<\/span>\n  (\n    'Insert a timestamp with time zone literal'<\/span>,\n    TIMESTAMP<\/span> '2017-08-08 2:00:00 -08:00'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s how the data is stored in the table:<\/p>\n\n\n\n
\"Oracle<\/figure>\n\n\n\n
Step 8. Use the result of the CURRENT_TIMESTAMP<\/a><\/code> function for inserting:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  logs<\/span> (log_message, created_at)\nVALUES<\/span>\n  (\n    'Use current_timestamp function'<\/span>,\n    CURRENT_TIMESTAMP<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Oracle inserted the current time stamp with the session time zone into the table:<\/p>\n\n\n\n
\"Oracle<\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the Oracle TIMESTAMP WITH TIME ZONE<\/code> data type for storing date and time data that includes time zones.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Step 3. insert a TIMESTAMP<\/a><\/code> value into the created_at<\/code> column.<\/p>\n\n\n
Step 2. Check the session time zone<\/a>:<\/p>\n\n\n
Please see the Oracle date format<\/a> for detailed information on how to construct a date format model.<\/p>\n\n\n\n