Date/Time types

Date and time types

time, timestamp, and interval accept an optional precision value p. This value specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range for p is from 0 to 6.

The interval type has an additional option. You can restrict the set of stored fields by writing one of the following phrases:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND

Note that if `fields` and `p` are specified, `fields` must include `SECOND` because the precision applies only to seconds.

The type `time with time zone` is defined by the SQL standard, but the definition exhibits properties that can affect usability. In most cases, a combination of `date`, `time`, `timestamp without time zone`, and `timestamp with time zone` should provide a full range of date/time functionality required by any application.

### 1.  Date/time input

Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, and other formats. For some formats, the ordering of the day, month, and year in date input can be ambiguous. You can specify the expected order of these fields. Set the DateStyle parameter to `MDY` to select a month-day-year interpretation, `DMY` for day-month-year, and `YMD` for year-month-day.

This database is more flexible in handling date/time input than the SQL standard requires.

Remember that any date or time literal input needs to be enclosed in single quotes, like a text string. SQL requires the following syntax:

```sql
    type [ (p) ] 'value'

where p is an optional precision specification that specifies the number of fractional digits in the seconds field. You can specify precision for time, timestamp, and interval types. The precision can range from 0 to 6. If you do not specify a precision in a constant declaration, it defaults to the precision of the literal value, with a maximum of 6 digits.

Date

Date input

Time

The time-of-day types are time [ (``p``) ] without time zone and time [ (``p``) ] with time zone. time alone is equivalent to time without time zone.

Valid input for these types consists of a time of day followed by an optional time zone. If you specify a time zone in the input for time without time zone, it is silently ignored. You can also specify a date, but it is ignored unless you use a time zone that has a Daylight Saving Time (DST) rule, such as America/New_York. In this case, the date is required to determine whether standard time or DST applies. The appropriate time zone offset is recorded in the time with time zone value.

Time input

Time zone input

Timestamps

Valid input for the timestamp types consists of a concatenation of a date and a time, followed by an optional time zone, and an optional AD or BC. Alternatively, AD/BC can appear before the time zone, but this is not the preferred order. Thus:

    1999-01-08 04:05:06

and:

    1999-01-08 04:05:06 -8:00

are both valid values that follow the ISO 8601 standard. In addition, the widely used format:

    January 8 04:05:06 1999 PST

is also supported.

The SQL standard distinguishes timestamp without time zone and timestamp with time zone literals by the presence of a "+" or "-" symbol and a time zone offset after the time. Thus, according to the standard,

    TIMESTAMP '2004-10-19 10:23:54'

is a timestamp without time zone, whereas

    TIMESTAMP '2004-10-19 10:23:54+02'

is a timestamp with time zone. This database never examines the content of a literal string before determining its type. Therefore, it treats both of the preceding examples as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, you must specify the correct explicit type:

    TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

If a literal is determined to be timestamp without time zone, this database silently ignores any specified time zone. The resulting value is derived from the date and time fields in the input value and is not adjusted for a time zone.

For timestamp with time zone, the internally stored value is always in Universal Coordinated Time (UTC), traditionally known as Greenwich Mean Time (GMT). An input value with an explicit time zone is converted to UTC using the appropriate offset for that time zone. If no time zone is specified in the input string, it is assumed to be in the time zone specified by the system's `TimeZone` parameter. The value is then converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone and displayed as local time in that zone. To view the time in another time zone, you can either change timezone or use the AT TIME ZONE construct.

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value is interpreted as timezone local time. You can specify a different time zone for the conversion using AT TIME ZONE.

Special values

For convenience, this database supports some special date and time input values. The values infinity and -infinity have a special representation within the system and are displayed unchanged. The other values are notational shorthands that are converted to ordinary date and time values when they are 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.

Special date and time inputs

The following SQL-compatible functions can be used to retrieve the current time value for the corresponding data type: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP. Note that these are SQL functions and are not recognized in data input strings.

Date/Time output

The output format for date/time types can be set to one of four styles: ISO 8601, SQL (Ingres), the Unix date format, or German. The default is the ISO format. (The name 'ISO format' is a historical accident because the SQL standard requires the ISO 8601 format.) The output of the date and time types typically contains only the date or time part, respectively. However, the SQL style outputs date-only values in ISO format.

Date and time output styles

Date order conventions

The to_char formatting function is also available as a more flexible way to format date and time output.

Time zones

Time zones and time-zone conventions are influenced by political decisions along with the geometry of the Earth. Time zones around the world became somewhat standardized during the 19th century but continue to be prone to arbitrary changes, partly because of DST rules. This database uses the widely-used Internet Assigned Numbers Authority (IANA) (Olson) time zone database for information about historical time zone rules. For times in the future, it is assumed that the latest known rule for a given time zone will continue to be observed indefinitely.

This database is designed to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an unusual mix of date and time types and capabilities. Two obvious problems are:

• Although the date type has no associated time zone, the time type can. In the real world, however, time zones are meaningful only when associated with both a date and a time, because the offset can vary during the year with DST rules.

• The default time zone is specified as a constant numeric offset from UTC. It is therefore not possible to adapt to DST when performing date and time arithmetic across DST boundaries.

To overcome these difficulties, we recommend using date and time types that contain both date and time when you use time zones. We do not recommend using the type time with time zone (although this database supports it for legacy applications and for compliance with the SQL standard). This database assumes your local time zone for any type containing only a 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.

This database lets you specify time zones in three different forms:

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

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

• In addition to time zone names and abbreviations, this database accepts POSIX-style time zone specifications. This is not usually a preferred option for specifying a time zone, but it might be necessary if no suitable IANA time zone entry is available.

In short, there is a difference between abbreviations and full names. An abbreviation denotes a specific offset from UTC, whereas many full names imply a local DST rule, and so have two possible UTC offsets. For 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). Therefore, 2014-06-04 12:00 EDT specifies that same time instant. However, 2014-06-04 12:00 EST specifies noon Eastern Standard Time (UTC-5), regardless of whether DST was 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. This database interprets such abbreviations according to what they meant (or had most recently meant) on the specified date. However, this is not necessarily the same as local standard time on that day, as the EST example above shows.

In all cases, time zone names and abbreviations are case-insensitive. This is a change from versions of this database prior to 8.2, in which time zone names were case-sensitive in some contexts and case-insensitive in others.

Neither time zone names nor abbreviations are hard-wired into the server. They are retrieved from the configuration files stored in the .../share/timezone/ and .../share/timezonesets/ subdirectories of the installation directory.

You can set the `TimeZone` configuration parameter in the postgresql.conf file. You can also set it in the following ways:

• The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO, which is more SQL-compliant.

• libpq clients use the PGTZ environment variable to send a SET TIME ZONE command to the server upon connection.

Interval input

You can write interval values using the following syntax:

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

where `quantity` is a number (possibly signed). `unit` is `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 quantities of different units are implicitly added, taking signs into account. ago negates all the fields. This syntax is also used for interval output if `IntervalStyle` is set to postgres_verbose.

You can specify quantities of days, hours, minutes, and seconds without explicit unit markings. For example, '1 12:59:10' is read as '1 day 12 hours 59 min 10 sec'. Also, you can specify a combination of years and months with a dash. For example, '200-10' is read as '200 years 10 months'. In fact, these shorter forms are the only ones permitted by the SQL standard and are used for output when IntervalStyle is set to sql_standard.

You can also write interval values as ISO 8601 time intervals, using either the format with designators from section 4.4.X.X of the standard or the alternative format from section 4.4.X.X. The format with designators is as follows:

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

The string must start with a P, and can include a T that introduces the time-of-day units. The units can be omitted and can 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.

ISO 8601 interval unit abbreviations

If you use an override 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 specified 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 discards 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 minus sign in the interval literal '-1 2:03:04' applies to the days, hours, minutes, and seconds parts. This database allows the fields to have different signs and traditionally treats each field in the textual representation as independently signed. In this example, the hours, minutes, and seconds parts are considered positive. If IntervalStyle is set to sql_standard, a leading sign is considered to apply to all fields, but only if no additional signs appear. Otherwise, the traditional database interpretation is used. To avoid ambiguity, we recommend that you attach an explicit sign to each field if any field is negative.

In the verbose input format, and in some fields of the more compact input formats, field values can have fractional parts, such as '1.5 week' or '01:02:03.45'. Such input is converted to the appropriate number of months, days, and seconds for storage. When this would result in a fractional part in the months or days fields, the fraction is added to the lower-order fields, using the conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output.

Interval input

Internally, interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a DST adjustment is involved. The months and days fields are integers, whereas the seconds field can store fractions. 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

The functions justify_days and justify_hours are available to adjust days and hours that overflow their normal ranges.

Interval output

You can set the output format of the interval type to one of four styles: sql_standard, postgres, postgres_verbose, or iso_8601, using the SET intervalstyle command. The default is the postgres format.

The sql_standard style produces output that conforms to the SQL standard's specification for interval literal strings, if the interval value meets the SQL standard's restrictions (year-month only or day and 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 and time literal string, with explicit signs added to distinguish mixed-sign intervals.

Interval output style examples