PolarDB for Oracle supports the date and time data types described in this topic. All examples assume that the polar_comp_redwood_date configuration parameter is set to true when a table is created or altered.
Supported types
| Data type | Role | Storage size | Range | Precision |
|---|---|---|---|---|
DATE | Date and time, no time zone | 8 bytes | 4713 BC – 5874897 AD | 1 second |
TIMESTAMP [(p)] | Date and time, no time zone | 8 bytes | 4713 BC – 5874897 AD | 1 microsecond |
TIMESTAMP [(p)] WITH TIME ZONE | Date and time, with time zone | 8 bytes | 4713 BC – 5874897 AD | 1 microsecond |
TIME [(p)] | Time of day only | 8 bytes | 00:00:00 – 24:00:00 | 1 millisecond / 14 bits |
TIME [(p)] WITH TIME ZONE | Time of day with time zone | 12 bytes | 00:00:00+1459 – 24:00:00-1459 | 1 millisecond / 14 bits |
INTERVAL DAY TO SECOND [(p)] | Duration in days, hours, minutes, and seconds | 12 bytes | ±178,000,000 years | 1 microsecond / 14 bits |
INTERVAL YEAR TO MONTH | Duration in years and months | 12 bytes | ±178,000,000 years | 1 microsecond / 14 bits |
DATE, TIMESTAMP, TIME, and their WITH TIME ZONE variants each represent a point in time. INTERVAL types represent a duration between two points in time.DATE type behavior
When DATE appears as a column data type in a data definition language (DDL) statement such as CREATE TABLE or ALTER TABLE, the value is automatically stored as TIMESTAMP(0). Time fields are therefore preserved in the column.
When DATE appears in a Stored Procedure Language (SPL) context — as a variable type in a declaration section, as a formal parameter, or as a function return type — the value is also converted to TIMESTAMP(0), so DATE can process time fields in all these contexts.
TIMESTAMP precision
TIMESTAMP [(p)] accepts an optional precision value p that specifies the number of fractional digits retained in the seconds field. Valid values are 0 through 6; the default is 6.
By default, TIMESTAMP values are stored as double-precision floating-point numbers. In this case, the effective precision may be less than 6 for dates far from January 1, 2000. If TIMESTAMP is compiled as an 8-byte integer (a compile-time option), microsecond precision is available across the full supported range, but the range is limited to 4713 BC – 294276 AD.
TIMESTAMP [(p)] WITH TIME ZONE behaves identically to TIMESTAMP [(p)] but also stores the time zone.
INTERVAL types
An INTERVAL value represents a duration. PolarDB for Oracle supports two Oracle-compatible INTERVAL subtypes.
INTERVAL DAY TO SECOND
Stores a time interval as days, hours, minutes, and seconds. The optional precision p specifies the number of fractional digits in the seconds field.
| Example | Meaning |
|---|---|
INTERVAL '1 2:34:5.678' DAY TO SECOND(3) | 1 day, 2 hours, 34 minutes, 5.678 seconds |
INTERVAL '1 23' DAY TO HOUR | 1 day, 23 hours |
INTERVAL '2:34' HOUR TO MINUTE | 2 hours, 34 minutes |
INTERVAL '2:34:56.129' HOUR TO SECOND(2) | 2 hours, 34 minutes, 56.13 seconds (12.9 rounded to 13 at precision 2) |
INTERVAL YEAR TO MONTH
Stores a time interval as years and months.
| Example | Meaning |
|---|---|
INTERVAL '12-3' YEAR TO MONTH | 12 years, 3 months |
INTERVAL '45' YEAR | 45 years |
INTERVAL '300' MONTH | 25 years |
INTERVAL field valid values
Fields in an INTERVAL value must appear in descending order: YEAR before MONTH, DAY before HOUR, HOUR before MINUTE, MINUTE before SECOND.
| Field | Valid range |
|---|---|
YEAR | Any integer (positive or negative) |
MONTH | 0–11 |
DAY | Any integer (positive or negative) |
HOUR | 0–23 |
MINUTE | 0–59 |
SECOND | 0–59.9(p), where 9(p) is the fractional seconds precision |
Date and time input
Date and time input values must be enclosed in single quotation marks using the syntax:
type 'value'where type is DATE or TIMESTAMP, and value is a date or time string.
Use the TO_DATE function to avoid ambiguity when the input format is not guaranteed to be unambiguous.
Date input
The following formats are all equivalent to January 8, 1999. The ISO 8601 format (1999-01-08) is unambiguous regardless of locale settings and is the recommended format.
| Example | Notes |
|---|---|
1999-01-08 | ISO 8601 — unambiguous in all locale settings (recommended) |
January 8, 1999 | Unambiguous |
1999-Jan-08 | Unambiguous |
Jan-08-1999 | Unambiguous |
08-Jan-1999 | Unambiguous |
08-Jan-99 | Interpreted as January 8, 1999 |
Jan-08-99 | Interpreted as January 8, 1999 |
19990108 | ISO 8601 compact — unambiguous |
990108 | Interpreted as January 8, 1999 |
If a DATE value is not appended with a time, the hour, minute, and second fields default to zero.
Time input
| Example | Description |
|---|---|
04:05:06.789 | ISO 8601 |
04:05:06 | ISO 8601 |
04:05 | ISO 8601 |
040506 | ISO 8601 |
04:05 AM | Equivalent to 04:05 (AM has no effect) |
04:05 PM | Equivalent to 16:05 (hour must be ≤ 12) |
04:05:06.789-8 | ISO 8601 with UTC offset |
04:05:06-08:00 | ISO 8601 with UTC offset |
04:05-08:00 | ISO 8601 with UTC offset |
040506-08 | ISO 8601 with UTC offset |
04:05:06 PST | Time zone abbreviation |
2003-04-12 04:05:06America/New_York | Time zone by name |
Timestamp input
A timestamp combines a date and a time. The date and time fields follow the formats listed above.
| Format | Example |
|---|---|
Oracle default (dd-MON-yy) | 08-JAN-99 04:05:06 |
| ISO 8601 | 1999-01-08 04:05:06 |
Date and time output
The output format depends on the client:
| Client | Format | Example |
|---|---|---|
| psql | Redwood style (dd-MON-yy) | 31-DEC-05 07:37:16 |
| Java Database Connectivity (JDBC) | ISO 8601 (yyyy-mm-dd) | 1997-12-17 07:37:16 |
Internal formats
PolarDB calculates all date and time values using Julian dates, assuming 365.2425 days per year. This allows correct date calculations for dates as far back as 4713 BC.