The TIMESTAMP WITH LOCAL TIME ZONE data type is a variant of TIMESTAMP and is sensitive to time zone information. This topic describes the syntax, parameters, and usage notes of the TIMESTAMP WITH LOCAL TIME ZONE data type.
Syntax
TIMESTAMP [(scale)] WITH LOCAL TIME ZONE
Parameters
The threshold and precision of TIMESTAMP [(scale)] depend on the value of the scale parameter. Value range: [0, 9]. The maximum value 9 and minimum value 0 respectively specify that the value is accurate to nanoseconds and seconds. The default value 6 specifies that the value is accurate to microseconds.
Usage notes
The time zone information in the TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type is the time zone in which the current session occurs. Value range: [0001-01-01 00:00:00.000000000, 9999-12-31 23:59:59.999999999]. Compared with TIMASTAMP [(scale)] WITH TIME ZONE, this data type does not require you to enter the time zone information. OceanBase Database directly stores the timestamp in the default time zone +00:00. This time zone does not change. When you retrieve data, OceanBase Database returns the data in your local session time zone. This time zone can change. This datatype is useful for displaying date information in the time zone of the client system in a two-tier application. The default input and output format of the TIMESTAMP WITH LOCAL TIME ZONE data type is determined by NLS_TIMESTAMP_FORMAT. Run the following SQL statement to view the datetime format:
SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;
Result:
DD-MON-RR HH.MI.SSXFF AM
You can use a conversion function to define a data format. You can use the TO_CHAR (datetime,fmt) function to specify the data output format. This conversion function converts strings to the format specified by the fmt argument. If the fmt argument is not specified, TO_CHAR returns data in the default format of the data type. For more information about the input format of the TIMESTAMP WITH LOCAL TIME ZONE data type, see Timestamp literals.
The value of the TIMESTAMP WITH LOCAL TIME ZONE data type is not specified based on any special literal. The time zone information of the local session is returned based on the SESSIONTIMEZONE parameter. The value of SESSIONTIMEZONE is specified based on the value of the customized parameter TIME_ZONE, as shown in the following example:
obclient> CREATE TABLE LocalTZ ( ltzcol TIMESTAMP WITH LOCAL TIME ZONE);
Query OK, 0 rows affected
obclient> INSERT INTO LocalTZ VALUES (TIMESTAMP '2020-02-25 11:10:08.123');
Query OK, 1 row affected
obclient> ALTER SESSION SET TIME_ZONE='+08:00';
Query OK, 0 rows affected
obclient> SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
+-----------------+------------------------------+
| SESSIONTIMEZONE | LTZCOL |
+-----------------+------------------------------+
| +08:00 | 25-FEB-20 11.10.08.123000 AM |
+-----------------+------------------------------+
1 row in set
You can modify the value of SESSIONTIMEZONE by modifying the value of the custom parameter TIME_ZONE. For example:
obclient> ALTER SESSION SET TIME_ZONE='+00:00';
Query OK, 0 rows affected
obclient> SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
+-----------------+------------------------------+
| SESSIONTIMEZONE | LTZCOL |
+-----------------+------------------------------+
| +00:00 | 25-FEB-20 11.10.08.123000 AM |
+-----------------+------------------------------+
1 row in set