NEW_TIME function in oracle sql

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.