All Products
Search

This Product

    PolarDB:Date and time types

    Document Center

    PolarDB:Date and time types

    Last Updated:Mar 28, 2026

    PolarDB for PostgreSQL (Compatible with Oracle) supports the following date and time data types.

    Note The following discussion assumes that the polar_comp_redwood_date configuration parameter is set to true when a table is created or altered.

    Supported types

    TypeStorage sizeDescriptionMin valueMax valueResolution
    DATE8 bytesDate and time, without time zone4713 BC5874897 AD1 second
    INTERVAL DAY TO SECOND [(p)]12 bytesTime interval in days, hours, minutes, and seconds-178000000 years178000000 years1 microsecond / 14 bits
    INTERVAL YEAR TO MONTH12 bytesTime interval in years and months-178000000 years178000000 years1 microsecond / 14 bits
    TIMESTAMP [(p)]8 bytesDate and time, without time zone4713 BC5874897 AD1 microsecond
    TIMESTAMP [(p)] WITH TIME ZONE8 bytesDate and time, with time zone4713 BC5874897 AD1 microsecond
    TIME [(p)]8 bytesTime of day only00:00:0024:00:001 millisecond / 14 bits
    TIME [(p)] WITH TIME ZONE12 bytesTime of day with time zone00:00:00+145924:00:00-14591 millisecond / 14 bits

    DATE type

    When DATE appears as a column type in data definition language (DDL) statements such as CREATE TABLE or ALTER TABLE, PolarDB converts the DATE value to TIMESTAMP(0) when storing the table definition. Time fields are also stored.

    When DATE appears in Stored Procedure Language (SPL) — as a variable type in a declaration section, as a formal parameter type in an SPL procedure or function, or as the return type of an SPL function — PolarDB also converts the DATE value to TIMESTAMP(0), enabling DATE to store time fields.

    TIMESTAMP type

    TIMESTAMP accepts an optional precision value p that specifies the number of fractional digits retained in the seconds field. Valid values of p range from 0 to 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. Values are stored as seconds before or after midnight on January 1, 2000. Dates within a few years of January 1, 2000 achieve microsecond precision, but precision degrades for dates further away.

    When stored as an 8-byte integer (a compile-time option), microsecond precision is available for the full range of values. However, the supported date range is limited to 4713 BC to 294276 AD.

    TIMESTAMP [(p)] WITH TIME ZONE behaves identically to TIMESTAMP [(p)], but also stores the time zone.

    INTERVAL types

    INTERVAL values specify a period of time and are composed of the following fields:

    FieldValid values
    YEARAny integer (positive or negative)
    MONTH0 to 11
    DAYAny integer (positive or negative)
    HOUR0 to 23
    MINUTE0 to 59
    SECOND0 to 59.9(p), where 9(p) is the fractional seconds precision

    Fields must appear in descending order: YEAR before MONTH, DAY before HOUR, and HOUR before MINUTE before SECOND.

    PolarDB supports two Oracle-compatible INTERVAL types.

    INTERVAL DAY TO SECOND [(p)]

    Stores a time interval in days, hours, minutes, and seconds. p specifies the precision of the seconds field.

    Examples:

    LiteralValue
    INTERVAL '1 2:34:5.678' DAY TO SECOND(3)1 day, 2 hours, 34 minutes, 5 seconds, and 678 thousandths of a second
    INTERVAL '1 23' DAY TO HOUR1 day and 23 hours
    INTERVAL '2:34' HOUR TO MINUTE2 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 In the last example, the fractional second 0.129 is rounded up to 0.13 based on the specified precision of 2.

    INTERVAL YEAR TO MONTH

    Stores a time interval in years and months.

    Examples:

    LiteralValue
    INTERVAL '12-3' YEAR TO MONTH12 years and 3 months
    INTERVAL '45' YEAR45 years
    INTERVAL '300' MONTH25 years

    Date and time input

    Date and time input is accepted in ISO 8601 format, the Oracle default dd-MON-yy format, and other unambiguous formats. To avoid ambiguity, use the TO_DATE function.

    Enclose each date or time value in single quotation marks, using the following syntax:

    type 'value' type

    Where type is DATE or TIMESTAMP, and value is a date or time string.

    Date input

    The following formats are all 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

    If a DATE value is not followed by a time value, the hour, minute, and second fields are set to zero.

    Time input

    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 hour value must be 12 or less.
    04:05:06.789-8ISO 8601
    04:05:06-08:00ISO 8601
    04:05-08:00ISO 8601
    040506-08ISO 8601
    04:05:06 PSTTime zone abbreviation
    2003-04-12 04:05:06America/New_YorkTime zone by name

    Timestamp input

    A valid timestamp consists of a date followed by a time. The date and time components accept the formats described above.

    Oracle format:

    08-JAN-99 04:05:06

    ISO 8601 format:

    1999-01-08 04:05:06

    Date and time output

    The output format depends on the client interface:

    • Java Database Connectivity (JDBC): displays dates in ISO 8601 format (yyyy-mm-dd)

    • psql: displays dates in Redwood format (dd-MON-yy), which is Oracle-compatible

    FormatExample
    Redwood style31-DEC-05 07:37:16
    ISO 8601/SQL standard1997-12-17 07:37:16

    Internal formats

    PolarDB calculates all date and time values using Julian dates, assuming 365.2425 days per year. This ensures accurate date calculations for dates after 4713 BC.