Oracle NEW_TIME Function: A Simple Guide

The NEW_TIME function in Oracle SQL is a specific-purpose function used to convert a date and time from one time zone to another.

Important Note: This is an older Oracle function and only supports a very limited list of time zones. For modern, flexible time zone conversions, Oracle recommends using TIMESTAMP WITH TIME ZONE data types along with the FROM_TZ and AT TIME ZONE functions.

What is the NEW_TIME Function in Oracle?

The NEW_TIME(date, timezone1, timezone2) function answers the question: "If it is this date and time in timezone1, what is the date and time in timezone2?"

The function always returns a DATE value. To use it correctly, you should first set your session's date format to show the 24-hour time.

The only time zones supported are:

• AST/ADT: Atlantic Standard/Daylight Time
• BST/BDT: Bering Standard/Daylight Time
• CST/CDT: Central Standard/Daylight Time
• EST/EDT: Eastern Standard/Daylight Time
• GMT: Greenwich Mean Time
• HST/HDT: Alaska-Hawaii Standard/Daylight Time
• MST/MDT: Mountain Standard/Daylight Time
• NST: Newfoundland Standard Time
• PST/PDT: Pacific Standard/Daylight Time
• YST/YDT: Yukon Standard/Daylight Time

NEW_TIME Function Syntax

The syntax for NEW_TIME is as follows:

NEW_TIME(date, timezone1, timezone2)

Let's break that down:

• date: The input DATE value you want to convert.
• timezone1: The time zone of the input date (e.g., 'EST').
• timezone2: The target time zone you want to see the time in (e.g., 'PST').

A Note on Viewing Results

To see the time part of the DATE result, you must run this command in your SQL session first.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle NEW_TIME Function Examples

Here are two practical examples of how to use NEW_TIME.

Example 1: Converting from Eastern (EST) to Pacific (PST) Time using NEW_TIME

This example converts 10:00 AM Eastern Standard Time to its equivalent in Pacific Standard Time.

Query:

SELECT 
  NEW_TIME(
    TO_DATE('2025-11-20 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    'EST', 'PST'
  ) AS "Pacific_Time"
FROM DUAL;

Result: (PST is 3 hours behind EST)

Pacific_Time
--------------------
20-NOV-2025 07:00:00

Example 2: Converting from GMT to Central (CST) Time with NEW_TIME

This example converts a date and time from Greenwich Mean Time (GMT) to Central Standard Time (CST).

Query:

SELECT 
  NEW_TIME(
    TO_DATE('2025-07-14 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    'GMT', 'CST'
  ) AS "Central_Time"
FROM DUAL;

Result: (CST is 6 hours behind GMT, so 12:00 PM GMT is 6:00 AM CST)

Central_Time
--------------------
14-JUL-2025 06:00:00