Oracle NEW_TIME() Function

Oracle NEW_TIME() is a built-in function that converts a date from one specified time zone to another specified time zone and returns the result.

Before using this function, you must set NLS_DATE_FORMAT to display time in 24-hour format.

Oracle NEW_TIME() Syntax

Here is the syntax for the Oracle NEW_TIME() function:

NEW_TIME(date, timezone1, timezone2)

Parameters

date

Required.

timezone1

Required.

timezone2

Required.

The parameters timezone1 and timezone2 can be any of the following text strings:

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

Return Value

The Oracle NEW_TIME(date, timezone1, timezone2) function returns the date and time of date in the time zone of timezone2 converted from timezone1. The return type of the function is always DATE, regardless of the type of date.

If any of the parameters are NULL, NEW_TIME() will return NULL.

Oracle NEW_TIME() Examples

Here are several examples that demonstrate the usage of the Oracle NEW_TIME() function.

Basic Usage

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT
    NEW_TIME('2023-02-15 15:30:45', 'GMT', 'NST')
      "NEW_TIME('2023-02-15 15:30:45', 'GMT', 'NST')"
FROM dual;

Output:

NEW_TIME('2023-02-15 15:30:45', 'GMT', 'NST')
________________________________________________
2023-02-15 12:00:45

Current Time

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT
    NEW_TIME(CURRENT_DATE, 'GMT', 'NST')
      "NEW_TIME('2023-02-15 15:30:45', 'GMT', 'NST')"
FROM dual;

Output:

NEW_TIME('2023-02-15 15:30:45', 'GMT', 'NST')
________________________________________________
2023-02-13 07:27:40

In this example, we use CURRENT_DATE to get the current date and time.

Invalid Time Zone

If you specify an invalid time zone, Oracle will report an error.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT
    NEW_TIME(CURRENT_DATE, 'Err', 'NST')
FROM dual;

Output:

SQL Error: ORA-01857: not a valid time zone
01857. 00000 -  "not a valid time zone"

In this example, we use CURRENT_DATE to obtain the current date and time.

NULL Parameters

If any parameter is NULL, NEW_TIME() will return NULL.

SET NULL 'NULL';
SELECT
    NEW_TIME(NULL, 'GMT', 'NST') NULL_1,
    NEW_TIME(CURRENT_DATE, NULL, 'NST') NULL_2,
    NEW_TIME(CURRENT_DATE, 'GMT', NULL) NULL_3,
    NEW_TIME(NULL, NULL, NULL) NULL_4
FROM dual;

Output:

NULL_1    NULL_2    NULL_3    NULL_4
_________ _________ _________ _________
NULL      NULL      NULL      NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

Oracle NEW_TIME() is a built-in function that converts a date from one specified time zone to another and returns the result.