The time zone in a value of the TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type is the time zone where the current session occurs. When you specify a TIMASTAMP [(scale)] WITH TIME ZONE value, you must specify the time zone. However, when you specify a TIMESTAMP [(scale)] WITH LOCAL TIME ZONE value, ApsaraDB for OceanBase automatically stores the default database time zone (UTC+0). You cannot change the time zone. When you retrieve data, ApsaraDB for OceanBase returns the time zone of the current session and you can change the time zone. This data type is often used to store the date information that is always displayed in the time zone of the client system in a two-tier application.
Syntax
TIMASTAMP [(scale)] WITH TIME ZONE
Parameters
Parameter |
Value range |
Description |
|---|---|---|
| scale | 0~9 | The value of scale determines the bounds and precision of TIMESTAMP [(scale)]. The maximum value is 9, which indicates nanosecond precision. The minimum value is 0, which indicates second precision. The default value is 6. |
Format
NLS_TIMESTAMP_FORMAT determines the default input and output formats of the TIMESTAMP WITH LOCAL TIME ZONE data type. Execute the following SQL statement to query the date and time format:
SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;
The following result is returned:
DD-MON-RR HH.MI.SSXFF AM
To customize data formats, use conversion functions. You can use the TO_CHAR(datetime,fmt) function to specify the output data format. The conversion function converts a string to the format defined by the fmt parameter. If you do not specify fmt, TO_CHAR returns data in the default format of the data type. For more information about the input formats of the TIMESTAMP WITH TIME ZONE data type, see Timestamp literals.
Value range
0001-01-01 00:00:00.000000000 ~ 9999-12-31 23:59:59.999999999
Examples
No literals can be used to specify a TIMESTAMP WITH TIME ZONE value. SESSIONTIMEZONE returns the information of the local session time zone. The value of SESSIONTIMEZONE is the same as the value of the custom parameter TIME_ZONE .
CREATE TABLE LocalTZ ( ltzcol TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO LocalTZ VALUES (TIMESTAMP '2020-02-25 11:10:08.123');
ALTER SESSION SET TIME_ZONE='+08:00';
SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
The following result is returned:
+-----------------+------------------------------+
| SESSIONTIMEZONE | ltzcol |
+-----------------+------------------------------+
| +08:00 | 25-FEB-20 11:10:08.123000 AM |
+-----------------+------------------------------+
You can change SESSIONTIMEZONE by changing the value of the custom parameter TIME_ZONE :
ALTER SESSION SET TIME_ZONE='+00:00';
SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
The following result is returned:
+-----------------+------------------------------+
| SESSIONTIMEZONE | ltzcol |
+-----------------+------------------------------+
| +00:00 | 25-FEB-20 03:10:08.123000 AM |
+-----------------+------------------------------+