Purpose
The NEW_TIME function converts a date and time value from the source time zone to the target time zone based on a fixed offset between the two time zones, and returns the converted DATE value.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support it.
Syntax
NEW_TIME(date, timezone1, timezone2)
Parameters
Parameter |
Description |
|---|---|
| date | The date and time value to be converted. Valid values:DATE、TIMESTAMPand other date and time types. |
| timezone1 | Source time zone abbreviation, indicatingdateThe current time zone of the parameter. |
| timezone2 | The abbreviation of the target time zone, indicating the time zone after the conversion. |
Response type
Returns the DATE data type.
Supported Time Zone Abbreviations
timezone1 and timezone2 only support the following time zone abbreviations (case-insensitive):
abbreviation |
Meaning |
UTC Offset |
|---|---|---|
| AST | Atlantic Standard Time | -04:00 |
| ADT | Atlantic Daylight Time | -03:00 |
| BST | Bering Standard Time | -11:00 |
| BDT | Bering Daylight Time | -10:00 |
| CST | Central Standard Time | -06:00 |
| CDT | Central Daylight Time | -05:00 |
| EST | Eastern Standard Time | -05:00 |
| EDT | Eastern Daylight Time | -04:00 |
| GMT | Greenwich Mean Time | +00:00 |
| HST | Hawaii-Aleutian Standard Time | -10:00 |
| HDT | Hawaii-Aleutian Daylight Time | -09:00 |
| MST | Mountain Standard Time | -07:00 |
| MDT | Mountain Daylight Time | -06:00 |
| NST | Newfoundland Standard Time | -03:30 |
| PST | Pacific Standard Time | -08:00 |
| PDT | Pacific Daylight Time | -07:00 |
| YST | Yukon Standard Time | -09:00 |
| YDT | Yukon Daylight Time | -08:00 |
Usage instructions
- The function calculates the result based on a fixed time zone offset and does not account for historical changes in daylight saving rules.
- If any parameter is
NULL, the function returnsNULL. - An invalid time zone abbreviation will cause an error.
- An error will be returned if the conversion result exceeds the valid range of the
DATEtype.
Examples
Convert 12:00:00, January 15, 2024, Eastern Standard Time (EST) to Greenwich Mean Time (GMT).
SELECT NEW_TIME(TO_DATE('2024-01-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'EST', 'GMT') FROM DUAL;
The return result is as follows:
+---------------------------------------------------------------------------+
| NEW_TIME(TO_DATE('2024-01-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),'EST','GMT') |
+---------------------------------------------------------------------------+
| 2024-01-15 17:00:00 |
+---------------------------------------------------------------------------+
1 row in set