Oracle NEW_TIME

The Oracle NEW_TIME() function converts a date from a 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 zoneName
ADTAtlantic Daylight Time
ASTAtlantic Standard Time
BDTBering Daylight Time
BSTBering Standard Time
CDTCentral Daylight Time
CSTCentral Standard Time
EDTEastern Daylight Time
ESTEastern Standard Time
GMTGreenwich Mean Time
HDTAlaska-Hawaii Daylight Time
HSTAlaska-Hawaii Standard Time Time
MDTMountain Daylight Time
MSTMountain Standard Time
NSTNewfoundland Standard Time
PDTPacific Daylight Time
PSTPacific Standard Time
YDTYukon Daylight Time
YSTYukon 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 a 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:45
Code 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.

Was this tutorial helpful?