Oracle NEW_TIME Function is used to return the date in one time zone to a date in another time zone.
Oracle NEW_TIME Function Syntax
SELECT NEW_TIME(date,time_zone1, time_zone2) FROM table_name;
Here in the above Oracle NEW_TIME Function Syntax:
- ‘date’ is the date that has to be seen in another time zone.
- ‘time_zone1’ is the time zone from where the passed date is to be converted
- ‘time_zone2’ is the time zone to which the passed date is to be converted
Time_zone1 and time_zone2 can take the following values:
| Value | Explanation |
| AST | Atlantic Standard Time |
| ADT | Atlantic Daylight Time |
| BST | Bering Standard Time |
| BDT | Bering Daylight Time |
| CST | Central Standard Time |
| CDT | Central Daylight Time |
| EST | Eastern Standard Time |
| EDT | Eastern Daylight Time |
| GMT | Greenwich Mean Time |
| HST | Alaska-Hawaii Standard Time |
| HDT | Alaska-Hawaii Daylight Time |
| MST | Mountain Standard Time |
| MDT | Mountain Daylight Time |
| NST | Newfoundland Standard Time |
| PST | Pacific Standard Time |
| PDT | Pacific Daylight Time |
| YST | Yukon Standard Time |
| YDT | Yukon Daylight Time |
Oracle NEW_TIME – Converting GMT to PST Example
Oracle NEW_TIME Function can convert the time from one time zone to another.
For example, the Oracle NEW_TIME Function query below converts the GMT time to PST.
SELECT NEW_TIME(SYSDATE,'GMT','PST') FROM DUAL;
Will return something like “11/5/2012 4:24:04 AM”, here we have converted the sysdate time from GMT to PST time zone.
Oracle NEW_TIME Function – Use with SQL TO_DATE Example
Oracle NEW_TIME Function can be used with the SQL TO_DATE Function.
For example, the Oracle NEW_TIME Function query below uses SQL TO_DATE Function to convert String to Date in GMT and then returns PST.
SELECT NEW_TIME(TO_DATE('01-JAN-2012 02:30','DD-MON-YYYY HH24:MI'),PST,GMT)
FROM DUAL;
Will return something like “1/1/2012 10:30:00 AM” because we have converted the Pacific Standard Time to Greenwich Mean Time.