TIMESTAMP literals

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

TIMESTAMP literals

The TIMESTAMP[(scale)] data type stores values of year, month, day, hour, minute, second, and fractional second. When you specify a TIMESTAMP literal, you can specify the second field with a precision of up to nine digits of nanoseconds.

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

TIMESTAMP WITH TIME ZONE literals

A TIMESTAMP WITH TIME ZONE literal is a TIMESTAMP literal that contains time zone information.

The TIMESTAMP [(scale)] WITH TIME ZONE data type is a variant of the TIMESTAMP[(scale)] data type. It stores the time zone offset or time zone region name in addition to the values of year, month, day, hour, minute, second, and fractional second. When you specify a TIMESTAMP WITH TIME ZONE literal, you must specify the time zone information and you can specify the second field with a precision of up to nine digits of nanoseconds. The following example specifies the time zone field by using a time zone offset:

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

If two TIMESTAMP WITH TIME ZONE literals represent the same moment in the GMT time zone, they are considered the same literal even if their time zone fields have different values.

For example, 8:00 AM in the GMT-8 time zone and 11:00 AM in the GMT-5 time zone are the same moment:

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

You can also specify the time zone region name instead of the time zone offset in a literal. The following example replaces -08:00 with America/Los_Angeles:

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

In some regions, daylight saving time (DST) is implemented. To avoid ambiguity when DST is implemented, you can specify both the time zone region name (TZR) and the corresponding abbreviation (TZD) in a literal to ensure that the value of the literal is in DST:

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

TIMESTAMP WITH LOCAL TIME ZONE literals

The TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type stores values of year, month, day, hour, minute, second, and fractional second in the local time zone. OceanBase Database does not support the TIMESTAMP WITH LOCAL TIME ZONE literal. You can use other valid date and time literals to assign values to the TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type. The following table lists some formats that you can use to insert values into a TIMESTAMP WITH LOCAL TIME ZONE column and the corresponding values returned by queries: