Oracle NEW_TIME function

Last update on September 09 2024 11:53:15 (UTC/GMT +8 hours)

Generates a new date based on a given date and time zone and an input time zone in Oracle

The Oracle NEW_TIME() function is used to convert a given date from one time zone to another. It allows users to adjust a date and time value from an original time zone to a new time zone.

Uses of Oracle NEW_TIME() Function:

Converting a date between two time zones: Change a date and time value from one specified time zone to another.

Handling time zone conversions for scheduling: Adjust time-sensitive data for different regions in applications dealing with global users.

Facilitating cross-time zone reporting: Generate reports based on different time zones by converting timestamps accordingly.

Managing global date operations: Simplify operations that require coordinating times across various time zones.

Supporting daylight saving adjustments: Convert times taking into account standard and daylight time variations between regions.

Syntax:

NEW_TIME(date, timezone1, timezone2)

Parameters:

Name	Description
date	Specified date.
timezone1	Original timezone.
timezone2	New timezone.

The arguments timezone1 and timezone2 can be any of these text strings:

Value	Description
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

Pictorial Presentation

Examples: Oracle NEW_TIME() function

The following example returns a Central Standard Time, given the Pacific Standard time equivalent :

SQL> ALTER SESSION SET NLS_DATE_FORMAT =
  2     'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT NEW_TIME(TO_DATE(
  2     '03-04-2015 03:25:45', 'MM-DD-YY HH24:MI:SS'),
  3     'CST', 'PST') "New Date and Time" FROM DUAL;

Sample Output:

New Date and Time
--------------------
  04-MAR-2015 01:25:45

Previous: MONTHS_BETWEEN
Next: NEXT_DAY