OceanBase Database supports datetime data types, including DATE and TIMESTAMP.
DATE type
The DATE type stores datetime data. Although datetime can be represented by characters or numbers, the DATE type has unique attributes.
OceanBase Database stores DATE and TIMESTAMP data by using 64-bit INT values to record the number of microseconds from 00:00:00 on January 1, 1970.
Notice
The DATE and TIMESTAMP types fully support arithmetic operations. You can perform additions and subtractions on these types of data in the same way as those on NUMBER data.
The database displays datetime data in a specific format. DATE data is displayed in the DD-MON-RR format. For example, January 1, 2011 is displayed as 01-JAN-11. You can change the default format for a cluster or session.
OceanBase Database displays time data in the HH24::MI:SS format by default. If you do not specify the time part, the default value is 00::00:00. If the data contains only the time part, the default value of the date part is the first day of the current month.
TIMESTAMP type
The TIMESTAMP type is an extension of the DATE type, which additionally stores the fractional part of time. Therefore, this data type is suitable for storing accurate time values. For example, you can use this data type in an application in which the order of all events must be recorded. The TIMESTAMP type does not store time zone information.
The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE types store time zone information. You can use these two data types to record and analyze date information across geographical regions. The TIMESTAMP WITH TIME ZONE type stores time zone information. The time zone information is read, written, and processed in the database. The same column can contain values of different time zones. The TIMESTAMP WITH LOCAL TIME ZONE type does not store time zone information. In an Oracle database, the value of the TIMESTAMP WITH LOCAL TIME ZONE data type is converted to a time value of the DBTIMEZONE data type for storage. In an Oracle tenant of OceanBase Database, the DBTIMEZONE data type is fixed to the '+00:00' format. Therefore, the value of the TIMESTAMP WITH LOCAL TIME ZONE data type is converted to a UTC time value for storage. Data of the TIMESTAMP WITH LOCAL TIME ZONE type is displayed based on the time zone of the session.