All Products
Search
Document Center

TIMESTAMP WITH TIME ZONE data type

Last Updated: Jun 18, 2021

TIMESTAMP [(scale)] WITH TIME ZONE is a variant of TIMASTAMP [(scale)]. TIMESTAMP [(scale)] WITH TIME ZONE stores all the time information that is stored in a TIMASTAMP [(scale)] value, such as the year, month, day, hour, minute, and second values. The value of scale determines the bounds and precision of TIMESTAMP [(scale)] WITH TIME ZONE. The TIMESTAMP [(scale)] WITH TIME ZONE data type also stores the time zone information that TIMASTAMP [(scale)] does not store. Therefore, TIMESTAMP [(scale)] WITH TIME ZONE is often used to store the date and time information across geographical regions.

Syntax

TIMESTAMP [(scale)] WITH TIME ZONE

Parameters

Parameter

Value range

Description

scale

0~9

The value of scale determines the bounds and precision of TIMESTAMP [(scale)] WITH TIME ZONE. The maximum value of scale is 9, which indicates nanosecond precision. If scale is 9, nine digits appear after the decimal point of the second value. The minimum value of scale is 0, which indicates second precision. If scale is 0, no digits appear after the decimal point of the second value. The default value of scale is 6.

Format

NLS_TIMESTAMP_TZ_FORMAT determines the default input and output formats of the TIMESTAMP WITH TIME ZONE data type. Execute the following SQL statement to query the default formats:

SELECT @@NLS_TIMESTAMP_TZ_FORMAT FROM DUAL;

The following result is returned:

DD-MON-RR HH.MI.SSXFF AM TZR

To customize data formats, use conversion functions. When you insert data, you can use the TO_TIMESTAMP_TZ(char,fmt) function to specify the input format of the data. When you query data, you can use the TO_CHAR(datetime,fmt) function to specify the output format of the data. These two conversion functions convert a string to the format defined by the fmt parameter. If you do not specify fmt, the default format is used.

Value range

0001-01-01 00:00:00.000000000 ~ 9999-12-31 23:59:59.999999999

Examples

In the following examples, TO_TIMESTAMP_TZ(char,fmt) is used to insert a timestamp value.

When you insert a time zone, ApsaraDB for OceanBase allows you to use the offset and region name of the time zone.

  • The time zone offset is the difference (in hours and minutes) between the local time zone and the UTC+0 time zone.

  • Specify the time zone region name (TZR) and time zone abbreviation (TZD) in this format: Country/City Time zone abbreviation.

Use the time zone offset

Execute the following statement to insert a value by using the time zone offset:

SELECT TO_TIMESTAMP_TZ('2020-01-01 11:00:00 -05:00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

The following result is returned:

01-JUN-20 11.00.00.000000000 AM AMERICA/LOS_ANGELES

Use the region name and abbreviation of the time zone

Execute the following statement to insert a value by using the region name and abbreviation of the time zone:

SELECT TO_TIMESTAMP_TZ('2020-01-01 11:00:00 America/Los_Angeles PST','YYYY-MM-DD HH:MI:SS TZR TZD') FROM DUAL;

The following result is returned:

01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PST

Daylight saving time

ApsaraDB for OceanBase supports daylight saving time and uses time zone abbreviations to indicate the information about daylight saving time. For example, in the time zone region America/Los_Angeles, the daylight saving time is Pacific Daylight Time (PDT). PDT is in effect from the second Sunday in March to the first Sunday in November each year. Pacific Standard Time (PST) is in effect in the other periods of each year. If an inserted value contains only the name of the time zone region, ApsaraDB for OceanBase determines whether PDT applies to the inserted time zone region based on the inserted time information. The output includes the time zone abbreviation to indicate whether the current time is in PDT.

You can run the following sample code:

SELECT TO_TIMESTAMP_TZ('2020-02-01 11:00:00 America/Los_Angeles','YYYY-MM-DD HH:MI:SS TZR') FROM DUAL;
SELECT TO_TIMESTAMP_TZ('2020-06-01 11:00:00 America/Los_Angeles','YYYY-MM-DD HH:MI:SS TZR') FROM DUAL;

The following result is returned:

01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PST
01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PDT