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 without the time zone, accurate to seconds. |
| TIMESTAMP | An extended type of the DATE data type without the time zone, accurate to nanoseconds. |
| TIMESTAMP WITH TIME ZONE | An extended type of the DATE data type with the time zone, accurate to nanoseconds. |
| TIMESTAMP WITH LOCAL TIME ZONE | The timestamp with the local time zone, which is the time zone of the database. |
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 values 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 rules of the NLS calendar. |
Any positive or negative integer |
HOUR |
[00, 23] | [0, 23] |
MINUTE |
[00, 59] | [0, 59] |
SECOND |
[00, 59.9(n)] 9(n) specifies the precision of fractional seconds of time. 9(n) does not apply to DATE. |
[0, 59.9(n)] 9(n) specifies the precision of fractional seconds of an interval. |
TIMEZONE_HOUR |
[-12, 14]. This range applies to daylight saving time (DST). This range 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.