Oracle FROM_TZ Function: A Simple Guide

The FROM_TZ function in Oracle SQL is a straightforward conversion function. Its one and only job is to convert a TIMESTAMP value (which has no time zone information) into a TIMESTAMP WITH TIME ZONE value by "attaching" a time zone you specify.

This is extremely useful when you have local timestamp data and you need to give it context by assigning it to a specific time zone.

What is the FROM_TZ Function in Oracle?

The FROM_TZ(timestamp_value, time_zone_string) function takes a standard TIMESTAMP and a time zone string (like '-05:00' or 'US/Eastern') and combines them into a single TIMESTAMP WITH TIME ZONE data type.

It does not convert the time; it simply assigns the time zone to the timestamp value you provide.

FROM_TZ Function Syntax

The syntax for FROM_TZ requires two arguments:

FROM_TZ(timestamp_value, time_zone_string)

Let's break that down:

• timestamp_value: The TIMESTAMP value you want to convert.
• time_zone_string: The time zone you want to attach. This can be:
  • An offset (e.g., '-05:00', '+03:00').
  • A region name (e.g., 'US/Pacific', 'Europe/London').

Oracle FROM_TZ Function Examples

Here are two practical examples of how to use FROM_TZ.

Example 1: Attaching a Time Zone Offset

This example takes a simple TIMESTAMP and assigns it the time zone offset '-05:00' (representing US Eastern Time, for example).

Query:

SELECT 
  FROM_TZ(TIMESTAMP '2025-11-20 09:30:00', '-05:00') AS "TS_With_Offset"
FROM DUAL;

Result: (The output now includes the -05:00 offset)

TS_With_Offset
---------------------------------------------------------------------------
20-NOV-25 09.30.00.000000 AM -05:00

Example 2: Attaching a Time Zone Region Name

This example is often more robust, as region names automatically handle rules like Daylight Saving Time. Here, we assign a timestamp to the 'Europe/Paris' time zone.

Query:

SELECT 
  FROM_TZ(TIMESTAMP '2025-07-14 14:00:00', 'Europe/Paris') AS "TS_With_Region"
FROM DUAL;

Result: (The output now includes the region name)

TS_With_Region
---------------------------------------------------------------------------
14-JUL-25 02.00.00.000000 PM EUROPE/PARIS