This topic describes the date and time data types supported by PolarDB for PostgreSQL(Compatible with Oracle) Distributed Relational Database Service (DRDS).

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.
Data and time typeStorage sizeDescriptionMinimum valueMaximum valuePrecision
DATE8 bytesThe date and time without the time zone. 4713 BC5874897 AD1 second
INTERVAL DAY TO SECOND [(p)]12 bytesPeriod of time with custom accuracy. -178000000 years178000000 years1 microsecond / 14 bits
INTERVAL YEAR TO MONTH12 bytesThe list of time ranges. -178000000 years178000000 years1 microsecond / 14 bits
TIMESTAMP [(p)]8 bytesThe timestamp of the date and time. 4713 BC5874897 AD1 microsecond
TIMESTAMP [(p)] WITH TIME ZONE8 bytesThe timestamp of the date and time with the time zone. 4713 BC5874897 AD1 microsecond
TIME [(p)]8 bytesOnly for the time of a day. 00:00:0024:00:001 millisecond / 14 bits
TIME [(p)] WITH TIME ZONE12 bytesOnly for the time of a day with time zone. 00:00:00+145924:00:00-14591 millisecond / 14 bits

Assume that the DATE keyword appears as the data type of a column of a database table in data definition language (DDL) statements such as CREATE TABLE or ALTER TABLE. In this case, the DATE value is automatically converted to the TIMESTAMP(0) type when the definition of the table is stored in the database. Therefore, time fields in the DATE value are 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 output of an SPL function. In this case, the DATE value is converted to the TIMESTAMP(0) type. Therefore, DATE can also process time fields.

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 January 1, 2000, 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 types

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.

FieldValid value
YEARInteger value (positive or negative).
MONTH0 to 11.
DAYInteger value (positive or negative).
HOUR0 to 23.
MINUTE0 to 59.
SECOND0 to 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 MySQL supports two INTERVAL types that are compatible with Oracle.

  • The first variable supported by PolarDB for MySQL 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.
    Meanings of the following values in PolarDB for MySQL:
    • 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 MySQL is INTERVAL YEAR TO MONTH. This variable stores a time interval in years and months.
    Meanings of the following values in PolarDB for MySQL:
    • INTERVAL '12-3' YEAR TO MONTH

      12 years and 3 months.

    • INTERVAL '45' YEAR

      45 years.

    • INTERVAL '300' MONTH

      25 years.

Date and time input

Date and time input is 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 or time string.
  • Date
    The following input formats for dates are supported. 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 the sample time fields of dates or timestamps.
    ExampleDescription
    04:05:06.789ISO 8601
    04:05:06ISO 8601
    04:05ISO 8601
    040506ISO 8601
    04:05 AMEquivalent to 04:05. AM does not affect the value.
    04:05 PMEquivalent to 16:05. The value of the hour must be less than or equal to 12.
    04:05:06.789-8ISO 8601
    04:05:06-08:00ISO 8601
    04:05-08:00ISO 8601
    040506-08ISO 8601
    04:05:06 PSTThe abbreviation of the time zone.
    2003-04-12 04:05:06America/New_YorkThe time zone declared by name.
  • Timestamp

    A valid input for a timestamp consists of a date and a time. The date field of the timestamp can be formatted based on the preceding input formats for dates. The time field of the timestamp can be formatted based on the preceding input formats for times.

    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.
FormatExample
Redwood style31-DEC-05 07:37:16
ISO 8601/SQL standard1997-12-17 07:37:16

Internal formats

PolarDB for MySQL 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 correctly predict and calculate a date after 4713 BC.