The following table lists the date and time types supported by PolarDB-O.
|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 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.
|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-O supports two INTERVAL types that are compatible with Oracle.
- The first variable supported by PolarDB-O 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-O.
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-O 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-O.
INTERVAL '12-3' YEAR TO MONTH
12 years and three months.
INTERVAL '456' YEAR(2)
12 years and three months.
INTERVAL '300' MONTH
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.
type 'value' type
- type can be DATE or TIMESTAMP.
- value is a date and time string.
The following list describes some supported input formats for dates. Each value is equivalent to January 8, 1999.
- January 8, 1999
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.
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
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.
In the following example, the ISO 8601 standard format is used.
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.
|Redwood style||31-DEC-05 07:37:16|
|ISO 8601/SQL standard||1997-12-17 07:37:16|
PolarDB-O 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.