Date/time types v14

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

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

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

DATE can appear as a data type of:

• A variable in an SPL declaration section
• The data type of a formal parameter in an SPL procedure or an SPL function
• The return type of an SPL function

In these cases, it's always translated to TIMESTAMP and thus can handle a time component if present.

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

When TIMESTAMP values are stored as double-precision floating-point numbers (the default), the effective limit of precision might 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 8-byte integers (a compile-time option), microsecond precision is available over the full range of values. However, 8-byte integer timestamps have a more limited range of dates than shown in the table: from 4713 BC up to 294276 AD.

TIMESTAMP (p) WITH TIME ZONE is similar to TIMESTAMP (p) but includes the time zone as well.

INTERVAL types

INTERVAL values specify a period of time. Values of INTERVAL type are composed of fields that describe the value of the data. The following table lists the fields allowed in an INTERVAL type.

The fields must be presented in descending order, from YEARS to MONTHS and from DAYS to HOURS, MINUTES, and then SECONDS.

EDB Postgres Advanced Server supports two INTERVAL types compatible with Oracle databases.

The first variation supported by EDB Postgres Advanced Server is INTERVAL DAY TO SECOND [(p)]. INTERVAL DAY TO SECOND [(p)] stores a time interval in days, hours, minutes, and seconds.

p specifies the precision of the second field.

EDB Postgres Advanced Server interprets this value as as 1 day, 2 hours, 34 minutes, 5 seconds and 678 thousandths of a second:

INTERVAL '1 2:34:5.678' DAY TO SECOND(3)

EDB Postgres Advanced Server interprets this value as 1 day and 23 hours:

INTERVAL '1 23' DAY TO HOUR

EDB Postgres Advanced Server interprets this value as 2 hours and 34 minutes:

INTERVAL '2:34' HOUR TO MINUTE

EDB Postgres Advanced Server interprets this value as 2 hours, 34 minutes, 56 seconds and 13 thousandths of a second. The fractional second is rounded up to 13 because of the specified precision.

INTERVAL '2:34:56.129' HOUR TO SECOND(2)

The second variation supported by EDB Postgres Advanced Server that's compatible with Oracle databases is INTERVAL YEAR TO MONTH. This variation stores a time interval in years and months.

EDB Postgres Advanced Server interprets this value as 12 years and 3 months:

INTERVAL '12-3' YEAR TO MONTH

EDB Postgres Advanced Server interprets this value as 12 years and 3 months:

INTERVAL '456' YEAR(2)

EDB Postgres Advanced Server interprets this value as 25 years:

INTERVAL '300' MONTH

Date/time input

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's no ambiguity as to which component is the year, month, and day. However, we strongly recommend using the TO_DATE function to avoid ambiguities.

Enclose any date or time literal input 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.

Dates

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

Example
January 8, 1999
1999-01-08
1999-Jan-08
Jan-08-1999
08-Jan-1999
08-Jan-99
Jan-08-99
19990108
990108

You can assign the date values to a DATE or TIMESTAMP column or variable. The hour, minute, and seconds fields is set to zero if you don't append the date value with a time value.

Times

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

Time stamps

Valid input for time stamps consists of a concatenation of a date and a time. You can format the date portion of the time according to any of the examples shown in Dates. The time portion of the time stamp can be formatted according to any of examples shown in the table in Times.

This example shows a time stamp that follows the Oracle default format:

08-JAN-99 04:05:06

This example shows a time stamp that follows the ISO 8601 standard:

1999-01-08 04:05:06

Date/time output

The default output format of the date/time types is either:

• (dd-MON-yy), referred to as the Redwood date style, compatible with Oracle databases
• (yyyy-mm-dd) referred to as the ISO 8601 format

The format you use depends on 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.

Internals

EDB Postgres Advanced Server uses Julian dates for all date/time calculations. Julian dates correctly predict or calculate any date after 4713 BC based on the assumption that the length of the year is 365.2425 days.