NEW_TIME

The NEW_TIME function in Oracle is used to convert a date or timestamp from one time zone to another. This function is useful when you need to adjust time values between two time zones without manually accounting for time differences.

 

Syntax:

NEW_TIME(date_value, from_timezone, to_timezone)

• date_value: The date or timestamp value that you want to convert from one time zone to another.
• from_timezone: The time zone of the date_value (source time zone).
• to_timezone: The time zone to which you want to convert the date (destination time zone).

 

Return Type:

The return type is a DATE value, which will represent the same time in the new time zone. The returned value is always in the date format (without a time zone), even if the original value had a time zone.

 

Supported Time Zones:

Oracle supports specific time zone abbreviations for NEW_TIME, such as:

• 'PST' - Pacific Standard Time
• 'PDT' - Pacific Daylight Time
• 'EST' - Eastern Standard Time
• 'EDT' - Eastern Daylight Time
• 'CST' - Central Standard Time
• 'CDT' - Central Daylight Time
• 'GMT' - Greenwich Mean Time (UTC)
• 'UTC' - Coordinated Universal Time
• 'BST' - British Summer Time

These abbreviations typically represent different daylight-saving time (DST) schedules, and NEW_TIME takes care of the time difference based on the given time zone.

 

Key Points to Remember:

1.     No Time Zone Information in the Result:

• The result of NEW_TIME is always a DATE (which does not carry time zone information). It simply adjusts the date and time based on the conversion between the time zones specified.

2.     Time Zone Abbreviations:

• NEW_TIME works with time zone abbreviations like 'PST', 'EST', 'UTC', etc. These are not the same as the full time zone names used in TIMESTAMP WITH TIME ZONE or INTERVAL data types.

3.     No DST Adjustment:

• The function adjusts the date and time as if the specified time zones do not follow Daylight Saving Time (DST). If you need more granular control over DST or to convert to TIMESTAMP WITH TIME ZONE, you may need to handle that logic manually.

 

Examples of Using NEW_TIME:

1. Convert from PST to EST:

SELECT NEW_TIME(DATE '2025-08-15 10:00:00', 'PST', 'EST') FROM dual;

-- Result: 15-AUG-2025 01:00:00 PM

Explanation: The time 2025-08-15 10:00:00 PST is converted to 2025-08-15 01:00:00 PM EST.

2. Convert from GMT to PST:

SELECT NEW_TIME(DATE '2025-08-15 15:00:00', 'GMT', 'PST') FROM dual;

-- Result: 15-AUG-2025 07:00:00 AM

Explanation: The time 2025-08-15 15:00:00 GMT is converted to 2025-08-15 07:00:00 AM PST.

3. Convert from UTC to CST:

SELECT NEW_TIME(DATE '2025-08-15 12:00:00', 'UTC', 'CST') FROM dual;

-- Result: 15-AUG-2025 06:00:00 AM

Explanation: The time 2025-08-15 12:00:00 UTC is converted to 2025-08-15 06:00:00 AM CST.

4. Convert from PDT to BST:

SELECT NEW_TIME(DATE '2025-08-15 08:00:00', 'PDT', 'BST') FROM dual;

-- Result: 15-AUG-2025 05:00:00 PM

Explanation: The time 2025-08-15 08:00:00 PDT is converted to 2025-08-15 05:00:00 PM BST.

 

Limitations of the NEW_TIME Function:

1.     No Daylight Saving Time (DST) Handling:

• The function does not automatically account for Daylight Saving Time (DST) transitions. You must manually adjust the time zone abbreviations to reflect DST when needed. For example, if you are converting from PST to PDT (which handles Daylight Saving Time), you would need to adjust accordingly.

2.     Limited Time Zone Abbreviations:

• Oracle NEW_TIME supports only a fixed set of time zone abbreviations, which may not cover all possible time zones around the world (for instance, full IANA time zone names like 'America/Los_Angeles' are not supported). For more advanced time zone handling, you may need to use the AT TIME ZONE clause with TIMESTAMP WITH TIME ZONE.

3.     Date Format:

• The result is always returned as a DATE data type, meaning there is no explicit time zone information in the result. If you need more detailed time zone support, you may need to use a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data type.

4.     Time Zone Abbreviations May Vary:

• Time zone abbreviations can sometimes be ambiguous or may change depending on the Oracle database version or the underlying operating system. It's important to ensure that you use the correct abbreviation for your source and target time zones.

 

Use Cases for NEW_TIME:

1.     Scheduling Applications:

• In systems that deal with scheduling across different time zones (e.g., global conference calls or event management), NEW_TIME can be used to ensure that the time is converted to the appropriate time zone for the intended audience or region.

2.     International Data Conversion:

• When dealing with data from multiple countries or regions, NEW_TIME allows you to adjust timestamps to match the relevant time zone of each region.

3.     Time Zone Adjustment for Reports:

• If you are preparing reports for clients or stakeholders in different time zones, NEW_TIME can help convert the timestamp to the correct local time zone before generating the report.

 

Summary:

The NEW_TIME function in Oracle is a simple and efficient way to convert date and time values between two specified time zones using time zone abbreviations. While it does not handle all time zone details (such as Daylight Saving Time or full time zone names), it provides an easy way to shift time values across common time zones like PST, EST, GMT, and others. For more sophisticated time zone conversions or to work with more precise time zone data, you may need to use other functions such as AT TIME ZONE with TIMESTAMP WITH TIME ZONE.