OceanBase Database supports datetime and interval data types, which are consistent with those in Oracle Database. This topic provides an overview of the datetime and interval data types supported in the current version of OceanBase Database.
Datetime data types
Datetime data types are used to store date and time values. The following table describes the datetime data types supported in the current version of OceanBase Database.
| Data type | Description |
|---|---|
| DATE | Stores date and time information with second-level precision without time zone information. |
| TIMESTAMP | An extension of the DATE data type with nanosecond-level precision without time zone information. |
| TIMESTAMP WITH TIME ZONE | An extension of the DATE data type with nanosecond-level precision with time zone information. |
| TIMESTAMP WITH LOCAL TIME ZONE | A TIMESTAMP value with local time zone information. |
Interval data types
Unlike datetime data types, which store specific points in time, interval data types store periods of time. Interval data types can store the difference between two datetime values. The following table describes the interval data types supported in the current version of OceanBase Database.
| Data type | Description |
|---|---|
| INTERVAL YEAR TO MONTH | Stores a period of time in years and months. |
| INTERVAL DAY TO SECOND | Stores a period of time in days, hours, minutes, and seconds. |
Relevant fields and values
Both datetime and interval values are composed of fields. The values of these fields determine the values of the data types.
| Field | Valid values for datetime | Valid values for interval |
|---|---|---|
YEAR |
-4712 ~ 9999 (excluding year 0) | Any positive or negative integer. |
MONTH |
01 ~ 12 | 0 ~ 11 |
DAY |
01 ~ 31 (subject to the values of MONTH and YEAR, based on the current NLS calendar parameters) |
Any positive or negative integer. |
HOUR |
00 ~ 23 | 0 ~ 23 |
MINUTE |
00 ~ 59 | 0 ~ 59 |
SECOND |
00 ~ 59.9(n), where 9(n) is the precision of the fractional seconds. The 9(n) part does not apply to DATE. |
0 ~ 59.9(n), where 9(n) is the precision of the fractional seconds. |
TIMEZONE_HOUR |
-12 ~ 14 (this range accommodates daylight saving time changes). Does not apply to DATE or TIMESTAMP. |
Not applicable |
TIMEZONE_MINUTE |
00 ~ 59. Does not apply to DATE or TIMESTAMP. |
Not applicable |
TIMEZONE_REGION |
Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view. Does not apply to DATE or TIMESTAMP. |
Not applicable |
TIMEZONE_ABBR |
Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view. Does not apply to DATE or TIMESTAMP. |
Not applicable |
More information
For more information about daylight saving time, see Daylight saving time.
For more information about calculations involving the
DATEandINTERVALdata types, see Calculations involving the DATE and INTERVAL data types.