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: