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.
