Date/time types v16

Overview

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.

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 the following 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 the following value as 1 day and 23 hours:

INTERVAL '1 23' DAY TO HOUR

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

INTERVAL '2:34' HOUR TO MINUTE

EDB Postgres Advanced Server interprets the following 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 the following value as 12 years and 3 months:

INTERVAL '12-3' YEAR TO MONTH

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

INTERVAL '456' YEAR(2)

EDB Postgres Advanced Server interprets the following 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

Special Values

PostgreSQL supports several special date/time input values for convenience, as shown in the following table. The values infinity and -infinity are specially represented inside the system and are displayed unchanged; the others are simply notational shorthands that are converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.) All of these values must be enclosed in single quotes when used as constants in SQL commands.

The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type:

CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP LOCALTIME LOCALTIMESTAMP

Note that these are SQL functions and are not recognized in data input strings.

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.

Time Zones

PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future.

PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are:

• Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries.

• The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

All time zone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

PostgreSQL allows you to specify time zones in three different forms:

• A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view. PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by other software.

• A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view. You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.

• PostgreSQL also accepts POSIX-style time zone specifications. This option is not normally preferable to using a named time zone, but it may be necessary if no suitable IANA time zone entry is available.

In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule and so have two possible UTC offsets. As an example, 2014-06-04 12:00 America/New_York represents noon local time in New York, which for this particular date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT specifies that same time instant. But 2014-06-04 12:00 EST specifies noon Eastern Standard Time (UTC-5), regardless of whether daylight savings was nominally in effect on that date.

To complicate matters, some jurisdictions have used the same time zone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST example above, this is not necessarily the same as local civil time on that date.

In all cases, time zone names and abbreviations are recognized case-insensitively. (This is a change from PostgreSQL versions prior to 8.2, which were case-sensitive in some contexts but not others.)

Neither time zone names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under .../share/timezone/ and .../share/timezonesets/ of the installation directory.

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways using server configuration. There are also some special ways to set it:

• The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

• The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

Interval Input

Interval values can be written using the following verbose syntax:

[@] quantity unit [quantity unit...] [direction]

where quantity is a number (possibly signed); unit is microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units; direction can be ago or empty. The at sign (@) is optional noise. The amounts of the different units are implicitly added with appropriate sign accounting. ago negates all the fields. This syntax is also used for interval output if IntervalStyle is set to postgres_verbose.

Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10' is read the same as '1 day 12 hours 59 min 10 sec'. Also, a combination of years and months can be specified with a dash; for example '200-10' is read the same as '200 years 10 months'. (These shorter forms are in fact the only ones allowed by the SQL standard, and are used for output when IntervalStyle is set to sql_standard.)

Interval values can also be written as ISO 8601 time intervals, using either the “format with designators” of the standard's section 4.4.3.2 or the “alternative format” of section 4.4.3.3. The format with designators looks like this:

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

The string must start with a P, and may include a T that introduces the time-of-day units. The following table provides the available unit abbreviations. Units may be omitted, and may be specified in any order, but units smaller than a day must appear after T. In particular, the meaning of M depends on whether it is before or after T.

In the alternative format:

P [ years-months-days ] [ T hours:minutes:seconds ]

the string must begin with P, and a T separates the date and time parts of the interval. The values are given as numbers similar to ISO 8601 dates.

When writing an interval constant with a fields specification, or when assigning a string to an interval column that was defined with a fields specification, the interpretation of unmarked quantities depends on the fields. For example INTERVAL '1' YEAR is read as 1 year, whereas INTERVAL '1' means 1 second. Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. For example, writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the seconds field, but not the day field.

According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example, the negative sign in the interval literal '-1 2:03:04' applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle is set to sql_standard then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it's recommended to attach an explicit sign to each field if any field is negative.

Field values can have fractional parts: for example, '1.5 weeks' or '01:02:03.45'. However, because interval internally stores only three integer units (months, days, microseconds), fractional units must be spilled to smaller units. Fractional parts of units greater than months are rounded to be an integer number of months, for example, '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and days are computed to be an integer number of days and microseconds, assuming 30 days per month and 24 hours per day, e.g., '1.75 months' becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as fractional on output.

The following table shows some examples of valid interval input.

Internally, interval values are stored as months, days, and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the microseconds field can store fractional seconds. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results:

SELECT EXTRACT(hours from '80 minutes'::interval);
 date_part
-----------
         1

SELECT EXTRACT(days from '80 hours'::interval);
 date_part
-----------
         0        

Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.

Interval Output

Using the command SET intervalstyle you can set the output format of the interval type to one of four styles: sql_standard, postgres, postgres_verbose, or iso_8601. The default is the postgres format. The table in this section shows examples of each output style.

The sql_standard style produces output that conforms to the SQL standard's specification for interval literal strings, if the interval value meets the standard's restrictions (either year-month only or day-time only, with no mixing of positive and negative components). Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.

The output of the postgres style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO.

The output of the postgres_verbose style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to non-ISO output.

The output of the iso_8601 style matches the “format with designators” described in the ISO 8601 standard.

• On this page
• Overview
• INTERVAL types
• Date/time input
• Date/time output
• Internals
• Time Zones
• Interval Input
• Interval Output