Oracle TO_TIMESTAMP_TZ Function: A Simple Guide

The TO_TIMESTAMP_TZ function in Oracle SQL is a powerful conversion function. Its job is to take a text string (like '2025-11-20 10:30:00 -05:00') and convert it into a TIMESTAMP WITH TIME ZONE data type.

This data type is Oracle's most precise timestamp, as it stores the date, time (with fractional seconds), and a time zone offset (like '-05:00') or region name (like 'US/Eastern'). This function is essential when you need to store time-zone-aware data from a text string.

What is the TO_TIMESTAMP_TZ Function in Oracle?

The TO_TIMESTAMP_TZ function "translates" a character string into a TIMESTAMP WITH TIME ZONE. To do this, you must provide a "format model" (fmt) that tells Oracle exactly how to read the string, including the time zone part.

A key modern feature of this function is the optional DEFAULT ... ON CONVERSION ERROR clause. This allows you to provide a fallback value (like NULL) if the conversion fails, which prevents your query from stopping with an error on "bad" data.

TO_TIMESTAMP_TZ Function Syntax

The syntax for TO_TIMESTAMP_TZ is:

TO_TIMESTAMP_TZ(char [DEFAULT return_value ON CONVERSION ERROR]
              [, fmt]
              [, 'nlsparam'])

Let's break that down:

• char: The text string you want to convert (e.g., '2025-11-20 10:30:00 US/Pacific').
• DEFAULT return_value ON CONVERSION ERROR (Optional): A safety feature. It provides a default value to return if the char string doesn't match the format, preventing the query from failing.
• [fmt] (Optional, but Highly Recommended): The "format model" that tells Oracle how to read the string.
• [nlsparam] (Optional): A string that specifies the language for month or day names.

Common Time Zone Format Models (fmt)

• TZH: Time zone hour (e.g., -05).
• TZM: Time zone minute (e.g., 00).
• TZR: Time zone region name (e.g., US/EASTERN).

Best Practice: Always specify the format model (fmt) to ensure your SQL is predictable and works for everyone.

Oracle TO_TIMESTAMP_TZ Function Examples

Here are two practical examples of how to use TO_TIMESTAMP_TZ.

Example 1: Converting a String with a Time Zone Offset using TO_TIMESTAMP_TZ

This is the most common use case. We have a string that includes a time and a numeric offset (like -08:00), and we want to convert it to a TIMESTAMP WITH TIME ZONE value.

Query:

SELECT
  TO_TIMESTAMP_TZ(
    '2025-12-01 11:00:00 -08:00',
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  ) AS "Converted_Timestamp"
FROM DUAL;

Result: (The output will be a TIMESTAMP WITH TIME ZONE value)

Converted_Timestamp
---------------------------------------------------------------------------
01-DEC-25 11.00.00.000000000 AM -08:00

Example 2: Handling Conversion Errors with TO_TIMESTAMP_TZ

This example uses the DEFAULT ON CONVERSION ERROR clause. The string '1999-13-01...' is invalid because 13 is not a valid month. This would normally cause an error and stop the query.

With the DEFAULT clause, the query won't stop; it will simply return NULL instead.

Query:

SELECT
  TO_TIMESTAMP_TZ(
    '1999-13-01 11:00:00 -08:00' DEFAULT NULL ON CONVERSION ERROR,
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  ) AS "Converted_Value"
FROM DUAL;

Result: (Instead of an ORA-01843: not a valid month error, Oracle returns the default value, which is NULL.)

Converted_Value
---------------------------------------------------------------------------
(NULL)