Timestamp literals

OceanBase Database supports three timestamp literals: TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

TIMESTAMP literal

The TIMESTAMP[(scale)] data type stores the year, month, day, hour, minute, second, and fractional second values. When you specify the TIMESTAMP literal, the second field can be accurate up to nanoseconds, which means 9 decimal places.

TIMESTAMP '2020-02-25 11:26:18.316'

TIMESTAMP WITH TIME ZONE literal

The TIMESTAMP WITH TIME ZONE literal is a timestamp literal containing the timezone information.

The TIMESTAMP [(scale)] WITH TIME ZONE data type is a variant of TIMESTAMP [(scale)]. In addition to the information contained in the TIMESTAMP [(scale)] data type, it further stores information such as the time zone offset or time zone name. When you specify the TIMESTAMP WITH TIME ZONE literal, you need to specify the time zone information, and the second field can be accurate up to nanoseconds, which means 9 decimal places. The following example specifies the value of the time zone field by using the time zone offset:

TIMESTAMP '2020-02-25 11:26:18.316 +08:00'

When two values in the TIMESTAMP WITH TIME ZONE literal represent the same time point in the GMT time zone, they are considered the same literal although their TIME ZONE field values are different.

In the following example, 8:00 a.m. in the GMT-8 time zone is at the same time point as 11:00 a.m. in the GMT-5 time zone:

TIMESTAMP '2020-04-25 08:26:18.316 -08:00'
TIMESTAMP '2020-04-25 11:26:18.316 -05:00'

Similarly, you can also replace the time zone offset with the time zone name in the literal value. In the following example, -08:00 is replaced with America/Los_Angeles:

TIMESTAMP '2020-02-01 11:00:00 America/Los_Angeles'

Some regions are subject to daylight saving time conversion. To clarify the daylight saving time conversion time, the time zone region name (TZR) can be used together with the corresponding daylight saving time abbreviation (TZD) to ensure that the literal value is a daylight saving time. See the following example:

TIMESTAMP '2020-06-01 11:00:00 America/Los_Angeles PDT'

TIMESTAMP WITH LOCAL TIME ZONE literal

The TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type contains local time zone information. OceanBase Database does not have a dedicated TIMESTAMP WITH LOCAL TIME ZONE literal. It uses a valid datetime literal to assign values to the TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type. The following table lists some formats that can be inserted into the TIMESTAMP WITH LOCAL TIME ZONE column and the corresponding values returned.