Menu

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 a TIMESTAMP.
  • 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 DATE to TIMESTAMP before using FROM_TZ().
  • Combine it with AT TIME ZONE or SYS_EXTRACT_UTC() for conversion.

See Oracle’s FROM_TZ documentation.