Menu

Oracle TIMESTAMP WITH LOCAL TIME ZONE: Behavior and Examples

Updated on

Oracle TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) normalizes values to the database time zone when storing them, but does not preserve the original time-zone offset or region in the column. When you query the value, Oracle presents it in the current session time zone. This differs from TIMESTAMP WITH TIME ZONE, which stores a time-zone displacement or region.

Type Stores time-zone information? Retrieval behavior
TIMESTAMP No Returns the same wall-clock fields; no time-zone conversion.
TIMESTAMP WITH TIME ZONE Yes, an offset or region Preserves the zone information with the timestamp value.
TIMESTAMP WITH LOCAL TIME ZONE No original offset or region Normalizes to DBTIMEZONE for storage and displays in SESSIONTIMEZONE.

Syntax and precision

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

The fractional-seconds precision can be from 0 to 9; the default is 6. Oracle has no literal whose type is TIMESTAMP WITH LOCAL TIME ZONE. Insert a TIMESTAMP or TIMESTAMP WITH TIME ZONE value, and Oracle converts it to TSLTZ.

See the session time-zone conversion

This example inserts an instant specified as noon at UTC+02:00. The same stored value displays as 05:00 when the session time zone is UTC-05:00 and as 10:00 when the session is UTC:

CREATE TABLE meetings (
    meeting_id NUMBER PRIMARY KEY,
    starts_at TIMESTAMP WITH LOCAL TIME ZONE NOT NULL
);

ALTER SESSION SET TIME_ZONE = '-05:00';

INSERT INTO meetings (meeting_id, starts_at)
VALUES (1, TIMESTAMP '2025-01-15 12:00:00 +02:00');

SELECT SESSIONTIMEZONE,
       TO_CHAR(starts_at, 'YYYY-MM-DD HH24:MI:SS') AS displayed_time
FROM meetings;

SESSIONTIMEZONE  DISPLAYED_TIME
---------------  -------------------
-05:00           2025-01-15 05:00:00

In a session whose time zone is UTC, selecting the same row shows 2025-01-15 10:00:00. The stored instant is unchanged; only the displayed wall-clock time follows the session.

CAST and session time zone

A TIMESTAMP value has no time zone. When you cast one to TSLTZ, Oracle interprets its wall-clock fields in the current session time zone:

ALTER SESSION SET TIME_ZONE = '-05:00';

SELECT TO_CHAR(
           CAST(TIMESTAMP '2025-01-15 12:00:00'
                AS TIMESTAMP WITH LOCAL TIME ZONE),
           'YYYY-MM-DD HH24:MI:SS'
       ) AS displayed_time
FROM DUAL;

This cast treats noon as noon in the -05:00 session. If the input represents an instant from another zone, use a TIMESTAMP WITH TIME ZONE value, such as a timestamp literal with an offset, before converting it to TSLTZ.

SYS_EXTRACT_UTC return value

SYS_EXTRACT_UTC() extracts UTC clock fields from a datetime value with a time zone. Its result is a TIMESTAMP value without a time-zone field; use a time-zone-aware value such as FROM_TZ() as input when the source wall-clock value needs an explicit zone:

SELECT SYS_EXTRACT_UTC(
           FROM_TZ(TIMESTAMP '2025-01-15 12:00:00', '+02:00')
       ) AS utc_timestamp
FROM DUAL;

UTC_TIMESTAMP
-------------------
2025-01-15 10:00:00

The value is a TIMESTAMP with UTC clock fields, not a TIMESTAMP WITH TIME ZONE. The displayed text depends on NLS_TIMESTAMP_FORMAT; use TO_CHAR() with an explicit format mask when the output string must be stable.

If the application must retain the original time-zone region or offset, use TIMESTAMP WITH TIME ZONE. TSLTZ is appropriate when the original zone is not needed and results should display in the database session’s local time zone. In a multi-tier application, set the database session time zone deliberately; it might represent the application server rather than the end user’s browser.

Further reading