Summary: in this tutorial, you will learn how to use the Oracle TIMESTAMP WITH TIME ZONE
data type to store date and time data that includes time zones.
Introduction to Oracle TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE
data type stores both time stamp and time zone data.
The time zone data can be time zone offset e.g., -07:00
which is a difference between local time and UTC time or time zone region name e.g.,Europe/London
The following expression specifies the TIMESTAMP WITH TIME ZONE
data type:
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
In this expression, fractional_seconds_precision
is an optional argument that specifies the number of digits in the fractional part of the SECOND
field.
The fractional_seconds_precision
ranges from 0 to 9. Its default value is 6.
Oracle considers two TIMESTAMP WITH TIME ZONE
values are equal if they represent the same value in UTC regardless of the time zone data.
For example, the following TIMESTAMP WITH TIME ZONE
values are equal:
TIMESTAMP '2017-08-09 07:00:00 -7:00' TIMESTAMP '2017-08-09 09:00:00 -5:00'
The following statement proves this:
SELECT
DECODE( TIMESTAMP '1999-01-15 8:00:00 -8:00',
TIMESTAMP '1999-01-15 11:00:00 -5:00',
'Equal',
'Not Equal' )
FROM
dual;
Noted that the DECODE()
function returns the third argument if the first second equals the second argument, otherwise, it returns the fourth argument.
It is a good practice to use the TIMESTAMP WITH TIME ZONE
data type for the application that stores across time zones e.g., event scheduling, banking, and booking application, etc.
TIMESTAMP WITH TIME ZONE
literals
The following format specifies a TIMESTAMP WITH TIME ZONE
literal:
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'
For example:
TIMESTAMP '2017-08-10 10:30:20.15 -07:00'
Besides the time zone offset, you can use time zone region name as follows:
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZR'
For example:
TIMESTAMP '2017-08-10 10:30:20.15 US/Pacific'
For some regions that have Daylight Saving Time and Standard Time, you should include the TZD
format element explicitly to avoid the ambiguity of boundary when the time switches from one to another.
For example, PST
for US/Pacific
standard time and PDT
for US/Pacific
daylight saving time. The following value ensures that Daylight Saving Time value is returned:
TIMESTAMP '2017-08-10 10:30:20.15 US/Pacific PDT'
If you don’t specify PDT
, Oracle interprets it as the Standard Time if the ERROR_ON_OVERLAP_TIME
session parameter is set to FALSE by default, or issues an error if ERROR_ON_OVERLAP_TIME
is set to TRUE.
Default format for TIMESTAMP WITH TIME ZONE
value
By default, the date format for TIMESTAMP WITH TIME ZONE
data type is controlled by the NLS_TIMESTAMP_TZ_FORMAT
parameter.
SELECT
value
FROM
V$NLS_PARAMETERS
WHERE
parameter = 'NLS_TIMESTAMP_TZ_FORMAT';
The default value in the Oracle Database is:
DD-MON-RR HH.MI.SSXFF AM TZR
If you want to change this default format, you use the ALTER SESSION SET
statement.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'format';
Please see the Oracle date format for the detailed information of how to construct a date format model.
Oracle TIMESTAMP WITH TIME ZONE
example
Let’s create a new table named logs
for the demonstration.
CREATE TABLE logs
(
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
log_message VARCHAR2(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
PRIMARY KEY(log_id)
);
The data type of the created_at
column is TIMESTAMP WITH TIME ZONE
. Next, we will show you various ways to insert data into this column.
First, let’s check the session time zone:
SELECT SESSIONTIMEZONE FROM dual;
Currently, it is set to:
-07:00
The following statement inserts a TIMESTAMP
value into the created_at
column.
INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp',TIMESTAMP '2017-08-08 2:00:00');
Because the time stamp value does not have time zone data, Oracle appended the session time zone before storing it.
SELECT * FROM logs;

The following statement inserts a date time in character string format into the created_at
column
INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp with timezone as a character string','08-AUG-2017 2:00:00 PM -05:00');
In this case, Oracle converted the character string to the corresponding TIMESTAMP WITH TIME ZONE
value.
See the following value inserted into the table:

The following statement inserts a TIMESTAMP WITH TIME ZONE
literal into the created_at
column:
INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp with time zone literal',TIMESTAMP '2017-08-08 2:00:00 -08:00');
Here is how the data stored in the table:

The following statement uses the result of the CURRENT_TIMESTAMP
function for inserting:
INSERT INTO logs(log_message, created_at)
VALUES('Use current_timestamp function',CURRENT_TIMESTAMP);
Oracle inserted the current time stamp with the session time zone into the table:

In this tutorial, you have learned how to use the Oracle TIMESTAMP WITH TIME ZONE
data type for storing date and time data that includes time zones.