Like Oracle, OceanBase Database also supports datetime and interval data types. This topic describes the datetime and interval data types supported by OceanBase Database.
Datetime data types
The datetime data types store date and time information. The following table lists the datetime data types supported by OceanBase Database.
| Data type | Description |
|---|---|
| DATE | The date and time, which is accurate to seconds and contains no time zone. |
| TIMESTAMP | It is an extended type of the DATE data type, which is accurate to nanoseconds and contains no time zone. |
| TIMESTAMP WITH TIME ZONE | It is an extended type of the DATE data type, which is accurate to nanoseconds and contains the time zone. |
| TIMESTAMP WITH LOCAL TIME ZONE | The TIMESTAMP with local time zone information, which stores database time zone. |
Interval data types
The interval data types store a period of time, unlike the datetime data types that store a specific point in time. The interval data types store the difference between two datetime values. The following table lists the interval data types.
| Data type | Description |
|---|---|
| INTERVAL YEAR TO MONTH | The time interval in years and months. |
| INTERVAL DAY TO SECOND | The time interval in days, hours, minutes, and seconds. |
Related fields and values
Datetime and interval data consist of fields, and the values of these fields decide the value of the data types.
| Field | Value range of datetime | Value range of interval |
|---|---|---|
YEAR |
[-4712,9999], excluding year 0. | Any positive or negative integer. |
MONTH |
[01,12] | [0,11] |
DAY |
[01,31]. The value range is determined by the values of MONTH and YEAR and complies with the rule of the NLS calendar. |
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 time fractional seconds. 9(n) does not apply to DATE. |
[0,59.9(n)], where 9(n) is the precision of interval fractional seconds. |
TIMEZONE_HOUR |
[-12,14]. This range applies to daylight saving time and does not apply to DATE or TIMESTAMP. |
N/A |
TIMEZONE_MINUTE |
[00,59]. This range does not apply to DATE or TIMESTAMP. |
N/A |
TIMEZONE_REGION |
You can query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view to get the value range. This range does not apply to DATE or TIMESTAMP. |
N/A |
TIMEZONE_ABBR |
You can query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view to get the value range. This range does not apply to DATE or TIMESTAMP. |
N/A |
More information
For more information about the DST, see DST.
For more information about the calculation of the
DATEandINTERVALdata types, see Calculation of dates, timestamps, and intervals.