The following table lists the date and time types supported by PolarDB for Oracle.

Note In the following discussion about the date and time types, assume that the polar_comp_redwood_date configuration parameter has been set to true when a table is created or altered.
Name Storage size Description Minimum value Maximum value Precision
DATE 8 bytes Date and time 4713 BC 5874897 AD 1 second
INTERVAL DAY TO SECOND [(p)] 12 bytes Period of time -178000000 years 178000000 years 1 microsecond / 14 digits
INTERVAL YEAR TO MONTH 12 bytes Period of time -178000000 years 178000000 years 1 microsecond / 14 digits
TIMESTAMP [(p)] 8 bytes Date and time 4713 BC 5874897 AD 1 microsecond
TIMESTAMP [(p)] WITH TIME ZONE 8 bytes Date and time with time zone 4713 BC 5874897 AD 1 microsecond

Assume that the DATE keyword appears as the data type of a column in data definition language (DDL) statements, such as CREATE TABLE or ALTER TABLE. In this case, the data of the DATE type is automatically converted to the TIMESTAMP(0) type at the time when the table definition is stored in the database. Therefore, a time component in the DATE value is also stored in the column.

Assume that one of the following situations occurs: The DATE keyword appears as the data type of a variable in a Stored Procedure Language (SPL) declaration section. The DATE keyword appears as the data type of a formal parameter in an SPL procedure or an SPL function. The DATE keyword appears as the return type of an SPL function. In this case, the DATE value is converted to the TIMESTAMP(0) type. Therefore, DATE can also process a time component.

TIMESTAMP accepts an optional precision value p that specifies the number of fractional digits retained in the seconds field. The valid values of p range from 0 to 6. The default value is 6.

By default, TIMESTAMP values are stored as double-precision floating-point numbers. In this case, the effective limit of precision can be less than 6. TIMESTAMP values are stored as seconds before or after midnight January 1, 2000. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When a TIMESTAMP value is stored as an 8-byte integer that is a compile-time option, microsecond precision is available for the full range of values. However, 8-byte integer timestamps have a more limited range of dates than the dates listed in the preceding table. The value ranges from 4713 BC to 294276 AD.

TIMESTAMP (p) WITH TIME ZONE is similar to TIMESTAMP (p), but the former type includes the time zone.

INTERVAL type

INTERVAL values specify a period of time. Values of the INTERVAL type are composed of the fields that describe the data values. The following table lists the fields that are allowed in an INTERVAL type.

Field name Value
YEAR Integer value (positive or negative)
MONTH 0 through 11
DAY Integer value (positive or negative)
HOUR 0 through 23
MINUTE 0 through 59
SECOND 0 through 59.9(p) where 9(p) is the precision of fractional seconds

The fields must appear in descending order, from YEARS to MONTHS, from DAYS to HOURS, and from MINUTES to SECONDS.

PolarDB for Oracle supports two INTERVAL types that are compatible with Oracle.

  • The first variable supported by PolarDB for Oracle is INTERVAL DAY TO SECOND [(p)]. This variable stores a time interval in days, hours, minutes, and seconds.
    Note p specifies the precision of the second field.
    The following list describes the semantic meaning of each INTERVAL value in PolarDB for Oracle. 
    • INTERVAL '1 2:34:5.678' DAY TO SECOND(3)

      One day, 2 hours, 34 minutes, 5 seconds, and 678 thousandths of a second.

    • INTERVAL '1 23' DAY TO HOUR

      One day and 23 hours.

    • INTERVAL '2:34' HOUR TO MINUTE

      2 hours and 34 minutes.

    • INTERVAL '2:34:56.129' HOUR TO SECOND(2)
      2 hours, 34 minutes, 56 seconds, and 13 thousandths of a second.
      Note The fractional second is rounded up to 13 because of the specified precision.
  • The second Oracle-compatible variable supported by PolarDB for Oracle is INTERVAL YEAR TO MONTH. This variable stores a time interval in years and months.
    The following list describes the semantic meaning of each INTERVAL value in PolarDB for Oracle. 
    • INTERVAL '12-3' YEAR TO MONTH

      12 years and three months.

    • INTERVAL '456' YEAR(2)

      12 years and three months.

    • INTERVAL '300' MONTH

      25 years.

Date and time input

Date and time input is accepted in the ISO 8601 SQL-compatible format, the default dd-MON-yy format in Oracle, and other formats that have clear year, month, and day values. However, we recommend that you use the TO_DATE function to avoid ambiguities.

Each date or time input must be enclosed in single quotation marks (') in the format of text strings. You can use the following standard SQL syntax: 
type 'value' type
Note
  • type can be DATE or TIMESTAMP.
  • value is a date and time string.
  • Date
    The following list describes some supported input formats for dates. Each value is equivalent to January 8, 1999.
    • January 8, 1999
    • 1999-01-08
    • 1999-Jan-08
    • Jan-08-1999
    • 08-Jan-1999
    • 08-Jan-99
    • Jan-08-99
    • 19990108
    • 990108

    The date values can be assigned to a DATE or TIMESTAMP column or variable. The hour, minute, and second fields are set to zero if the DATE value is not appended with a time value.

  • Time
    The following table lists some examples of the date or timestamp values that have time values.
    Example Description
    04:05:06.789 ISO 8601
    04:05:06 ISO 8601
    04:05 ISO 8601
    040506 ISO 8601
    04:05 AM Same as 04:05; AM does not affect value
    04:05 PM Same as 16:05; input hour must be <= 12
  • Timestamp

    A valid input value for a timestamp consists of a concatenation of a date and a time.

    In the following example, the default format of Oracle is used.

    08-JAN-99 04:05:06

    In the following example, the ISO 8601 standard format is used.

    1999-01-08 04:05:06

Date and time output

The default output format of the date and time type can be dd-MON-yy or yyyy-mm-dd. dd-MON-yy is compatible with Oracle and referred to as the Redwood date format. yyyy-mm-dd is the ISO 8601 format that is determined by the database programming interface. Programs that use Java Database Connectivity (JDBC) for SQL interaction display the date in the ISO 8601 format. Other programs such as PSQL display the date in the Redwood format.

The following table lists the examples of the outputs in the Redwood and ISO 8601 formats.
Format Example
Redwood style 31-DEC-05 07:37:16
ISO 8601/SQL standard 1997-12-17 07:37:16

Internal formats

PolarDB for Oracle calculates all the date and time values by using Julian dates. Assume that each year has 365.2425 days. In this case, you can use a Julian date to predict and calculate a date after 4713 BC in a correct way.