Oracle FROM_TZ: Convert TIMESTAMP to TIMESTAMP WITH TIME ZONE
Oracle FROM_TZ() combines a plain TIMESTAMP with a time-zone value and returns a TIMESTAMP WITH TIME ZONE.
It is useful when a timestamp represents local clock time but does not yet contain time-zone information.
Syntax
FROM_TZ(timestamp_value, time_zone_value)
Parameters
timestamp_value-
A value of type
TIMESTAMP. time_zone_value-
A character value containing either a numeric offset such as
'+09:00'or a time-zone region such as'America/New_York'.
Return Type
FROM_TZ() returns:
TIMESTAMP WITH TIME ZONE
It does not first shift the local clock fields; it attaches the specified time zone to the timestamp.
Using a Numeric Offset
SELECT FROM_TZ(
TIMESTAMP '2026-10-07 12:00:00',
'+09:00'
) AS result
FROM dual;
The result represents noon at UTC+09:00.
Using a Time-Zone Region
SELECT FROM_TZ(
TIMESTAMP '2026-07-01 09:00:00',
'America/New_York'
) AS result
FROM dual;
A region is often better than a fixed offset when daylight-saving rules matter because Oracle can determine the applicable offset for that date.
Convert the Result to UTC
Combine FROM_TZ() with SYS_EXTRACT_UTC():
SELECT SYS_EXTRACT_UTC(
FROM_TZ(
TIMESTAMP '2026-10-07 12:00:00',
'+08:00'
)
) AS utc_time
FROM dual;
This attaches +08:00 to the local timestamp and then converts the instant to UTC.
FROM_TZ with AT TIME ZONE
You can also keep a time-zone-aware result:
SELECT
FROM_TZ(
TIMESTAMP '2026-10-07 12:00:00',
'America/Los_Angeles'
) AT TIME ZONE 'UTC' AS utc_time
FROM dual;
NULL Values
A typed NULL timestamp can be passed to FROM_TZ():
SELECT FROM_TZ(
CAST(NULL AS TIMESTAMP),
'+00:00'
)
FROM dual;
The result is NULL.
An untyped NULL may produce a datatype error because Oracle must resolve the first argument as a TIMESTAMP.
If the time-zone argument is NULL, the result is also NULL.
Common Error: Passing DATE Directly
FROM_TZ() expects a TIMESTAMP as its first argument.
If your source is a DATE, cast it first:
SELECT FROM_TZ(
CAST(date_column AS TIMESTAMP),
'+08:00'
)
FROM your_table;
Summary
FROM_TZ()attaches a time zone to aTIMESTAMP.- The return type is
TIMESTAMP WITH TIME ZONE. - The time zone can be a numeric offset or a region name.
- Prefer a region when daylight-saving rules matter.
- Cast
DATEtoTIMESTAMPbefore usingFROM_TZ(). - Combine it with
AT TIME ZONEorSYS_EXTRACT_UTC()for conversion.
See Oracle’s FROM_TZ documentation.