Date and time types - PolarDB - Alibaba Cloud Documentation Center

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 type	Storage size	Description	Minimum value	Maximum value	Precision
DATE	8 bytes	The date and time without the time zone.	4713 BC	5874897 AD	1 second
INTERVAL DAY TO SECOND [(p)]	12 bytes	Period of time with custom accuracy.	-178000000 years	178000000 years	1 microsecond / 14 bits
INTERVAL YEAR TO MONTH	12 bytes	The list of time ranges.	-178000000 years	178000000 years	1 microsecond / 14 bits
TIMESTAMP [(p)]	8 bytes	The timestamp of the date and time.	4713 BC	5874897 AD	1 microsecond
TIMESTAMP [(p)] WITH TIME ZONE	8 bytes	The timestamp of the date and time with the time zone.	4713 BC	5874897 AD	1 microsecond
TIME [(p)]	8 bytes	Only for the time of a day.	00:00:00	24:00:00	1 millisecond / 14 bits
TIME [(p)] WITH TIME ZONE	12 bytes	Only for the time of a day with time zone.	00:00:00+1459	24:00:00-1459	1 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.


Field	Valid value
YEAR	Integer value (positive or negative).
MONTH	0 to 11.
DAY	Integer value (positive or negative).
HOUR	0 to 23.
MINUTE	0 to 59.
SECOND	0 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.


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 does not affect the value.
04:05 PM	Equivalent to 16:05. The value of the hour must be less than or equal to 12.
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601
04:05:06 PST	The abbreviation of the time zone.
2003-04-12 04:05:06America/New_York	The 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.


Format	Example
Redwood style	31-DEC-05 07:37:16
ISO 8601/SQL standard	1997-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.