Oracle NEW_TIME

The Oracle NEW_TIME() function converts a date from one time zone to another.

Note that before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.

Syntax #

The following illustrates the syntax of Oracle NEW_TIME() function:

NEW_TIME(date, from_timezone, to_timezone)
Code language: SQL (Structured Query Language) (sql)

Arguments #

The NEW_TIME() function accepts three arguments

1) date

A date whose time zone should be converted

2) from_timezone

A time zone of the date

3) to_timezone

A time zone to which the  date should be converted

The following table illustrates the permitted values for from_timezone and to_timezone:

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

Return value #

The NEW_TIME() function returns a value of the DATE type, regardless of the date argument.

Examples #

To demonstrate the NEW_TIME() function, first, we need to change the date format to 24-hour time format as follows:

ALTER SESSION SET NLS_DATE_FORMAT =  'DD-MON-YYYY HH24:MI:SS';
Code language: SQL (Structured Query Language) (sql)

The following example shows how to convert a date from one time zone to another:

SELECT
  NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ),  'AST', 'PST' ) TIME_IN_PST
FROM
  DUAL;Code language: SQL (Structured Query Language) (sql)

Here is the output:

06-AUG-2017 21:30:45Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle NEW_TIME() function to convert a date time value in one time zone to another.