All Products
Search
Document Center

Timestamp literals

Last Updated: Jun 18, 2021

ApsaraDB for OceanBase supports the following three types of timestamp literals:

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP literals

The TIMESTAMP[(scale)] data type stores year, month, day, hour, minute, and second, and fractional second values. When you specify a TIMESTAMP literal, you can set the maximum precision of the second field to the nanosecond that lies in the ninth digit.

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

TIMESTAMP WITH TIME ZONE literals

TIMESTAMP WITH TIME ZONE literals are timestamp literals that include time zone information.

The TIMESTAMP [(scale)] WITH TIME ZONE data type is a variant of the TIMESTAMP[(scale)] data type. The TIMESTAMP [(scale)] WITH TIME ZONE data type stores the information, such as time zone offsets and the names of time zone regions (TZRs), in addition to the information that the TIMESTAMP[(scale)] data type stores. When you specify a TIMESTAMP WITH TIME ZONE literal, you must specify the time zone information. You can also set the maximum precision of the second field to the nanosecond that lies in the ninth digit. In the following example, the time zone offset is used to specify the value of the time zone field.

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

Assume that the values of two TIMESTAMP WITH TIME ZONE literals represent the same point in time in the Greenwich Mean Time (GMT) time zone. The two literals are considered as the same literals even if the values of the time zone fields for these two literals are different. In the following example, 08:00:00 in the GMT-8 time zone and 11:00:00 in the GMT-5 time zone are actually the same point in time:

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

In a literal, we can replace the time zone offset with the name of a TZR. In the following example, -08:00 is replaced with America/Los_Angeles.

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

In some regions, the daylight saving time (DST) switches. To eliminate the time ambiguity when the DST switches, you can use both the TZR name and a corresponding abbreviation TZD. This ensures that the literal value is the daylight saving time.

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

TIMESTAMP WITH LOCAL TIME ZONE literals

TIMESTAMP [(scale)] WITH LOCAL TIME ZONE is a data type that contains the information about the local time zone. In ApsaraDB for OceanBase, specific TIMESTAMP WITH LOCAL TIME ZONE literals are unavailable. The TIMESTAMP [(scale)] WITH LOCAL TIME ZONE literals are assigned values by using other valid date and time literals. The following table shows some formats that can be used to insert values into the TIMESTAMP WITH LOCAL TIME ZONE column, and the return values of queries:

Value specified in the INSERT statement

Return value of the query

'25-FEB-20'

25-FEB-20 00.00.000000

SYSTIMESTAMP

25-FEB-20 14:28:41.264258

TO_TIMESTAMP('25-FEB-2020', 'DD-MON-YYYY')

25-FEB-20 00.00.000000

SYSDATE

25-FEB-20 02.55.29.000000 PM

TO_DATE('25-FEB-20', 'DD-MON-YYYY')

25-FEB-20 12.00.00.000000 AM

TIMESTAMP'2020-02-25 8:00:00 America/Los_Angeles'

25-FEB-20 08.00.00.000000 AM