The following discussion of the date/time types assumes that the configuration parameter, edb_redwood_date, has been set to true whenever a table is created or altered.

Postgres Plus Advanced Server supports the date/time types shown in the following table.

Table 3-5 Date/Time Types

Name	Storage Size	Description	Low Value	High Value	Resolution
DATE	8 bytes	Date and time	4713 BC	5874897 AD	1 second
TIMESTAMP [ (p) ]	8 bytes	Date and time	4713 BC	5874897 AD	1 microsecond

When DATE appears as the data type of a column in the data definition language (DDL) commands, CREATE TABLE or ALTER TABLE, it is translated to TIMESTAMP(0) at the time the table definition is stored in the database. Thus, a time component will also be stored in the column along with the date.

When DATE appears as a data type of a variable in an SPL declaration section, or the data type of a formal parameter in an SPL procedure or an SPL function, or the return type of an SPL function, it is always translated to TIMESTAMP(0) and thus can handle a time component if present.

TIMESTAMP accepts an optional precision value p which specifies the number of fractional digits retained in the seconds field. The allowed range of p is from 0 to 6 with the default being 6.

When TIMESTAMP values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6. TIMESTAMP values are stored as seconds before or after midnight 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When TIMESTAMP values are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a more limited range of dates than shown above: from 4713 BC up to 294276 AD.

Date and time input is accepted in ISO 8601 SQL-compatible format, the Oracle default dd-MON-yy format, as well as a number of other formats provided that there is no ambiguity as to which component is the year, month, and day. However, use of the TO_DATE function is strongly recommended to avoid ambiguities. See Section 3.5.6.

Any date or time literal input needs to be enclosed in single quotes, like text strings. The following SQL standard syntax is also accepted:

type 'value'

type is either DATE or TIMESTAMP. value is a date/time text string.

The following table shows some possible input formats for dates, all of which equate to January 8, 1999.

Table 3-6 Date Input

Example

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 seconds fields will be set to zero if the date value is not appended with a time value.

Some examples of the time component of a date or time stamp are shown in the following table.

Table 3-7 Time Input

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

Valid input for time stamps consists of a concatenation of a date and a time. The date portion of the time stamp can be formatted according to any of the examples shown in Table 3-6 Date Input. The time portion of the time stamp can be formatted according to any of examples shown in Table 3-7 Time Input.

The following is an example of a time stamp which follows the Oracle default format.

08-JAN-99 04:05:06

The following is an example of a time stamp which follows the ISO 8601 standard.

1999-01-08 04:05:06

The default output format of the date/time types will be either the Oracle compatible style (dd-MON-yy) referred to as the Redwood date style, or the ISO 8601 format (yyyy-mm-dd) depending upon the application interface to the database. Applications that use JDBC such as SQL Interactive always present the date in ISO 8601 form. Other applications such as PSQL present the date in Redwood form.

The following table shows examples of the output formats for the two styles, Redwood and ISO 8601.

Table 3-8 Date/Time Output Styles

Description	Example
Redwood style	31-DEC-05 07:37:16
ISO 8601/SQL standard	1997-12-17 07:37:16

Postgres Plus Advanced Server uses Julian dates for all date/time calculations. They have the nice property of correctly predicting/calculating any date more recent than 4713 BC to far into the future, using the assumption that the length of the year is 365.2425 days.