FROM_TZ() Function in Oracle

In Oracle Database, the FROM_TZ() function converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.

You pass the timestamp value and the time zone as two separate arguments, and the function returns them as a TIMESTAMP WITH TIME ZONE value.

Syntax

The syntax goes like this:

FROM_TZ(timestamp_value, time_zone_value)

Where timestamp_value is the timestamp, and time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

Example

Here’s an example:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '-04:00')
FROM DUAL;

Result:

01/JAN/30 12:30:35.000000000 PM -04:00

Specifying the Time Zone Region

Here’s an example that uses the time zone region instead of the time zone offset:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', 'America/St_Kitts')
FROM DUAL;

Result:

01/JAN/30 12:30:35.000000000 PM AMERICA/ST_KITTS

Change the Time Zone

You can use AT LOCAL or AT TIME ZONE clause to change the resulting timestamp to a different time zone.

Example:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', 'America/St_Kitts')
    AT TIME ZONE 'Asia/Bangkok'
FROM DUAL;

Result:

01/JAN/30 11:30:35.000000000 PM ASIA/BANGKOK

And here it is using AT LOCAL:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', 'America/St_Kitts')
    AT LOCAL
FROM DUAL;

Result:

02/JAN/30 02:30:35.000000000 AM AUSTRALIA/BRISBANE

You can use the SESSIONTIMEZONE function to check the time zone of the current session, and DBTIMEZONE to check the time zone of the database.

Invalid Time Zones

Passing an invalid time zone results in an error:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', 'Mars/Rover')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', 'Mars/Rover')
FROM DUAL
Error report -
ORA-01882: timezone region not found

You can get a list of valid time zone regions by querying the V$TIMEZONE_NAMES view.

Here’s what happens when we provide a time zone offset that’s outside the accepted range:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '+50:00')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '+50:00')
FROM DUAL
Error report -
ORA-01874: time zone hour must be between -15 and 15

Null Arguments

Passing null for the first argument results in an error:

SET NULL 'null';
SELECT 
    FROM_TZ(null, '+10:00')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT 
    FROM_TZ(null, '+10:00')
FROM DUAL
Error at Command Line : 2 Column : 13
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected TIMESTAMP got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Passing null for the second argument returns null:

SET NULL 'null';
SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', null)
FROM DUAL;

Result:

null

Note that by default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. In the example above, I specified that the string null should be returned.

Invalid Number of Arguments

Passing an invalid number of arguments results in an error:

SELECT FROM_TZ(TIMESTAMP '2030-01-01 12:30:35')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT FROM_TZ(TIMESTAMP '2030-01-01 12:30:35')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: