diff options
Diffstat (limited to 'doc/src/sgml/datatype.sgml')
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 75 |
1 files changed, 43 insertions, 32 deletions
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 5eb0cca..8dac308 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2452,7 +2452,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' the date and time. <productname>PostgreSQL</productname> accepts that format on input, but on output it uses a space rather than <literal>T</literal>, as shown above. This is for readability and for consistency with - <ulink url="https://tools.ietf.org/html/rfc3339">RFC 3339</ulink> as + <ulink url="https://datatracker.ietf.org/doc/html/rfc3339">RFC 3339</ulink> as well as some other database systems. </para> </note> @@ -2850,10 +2850,31 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>- </para> <para> - Field values can have fractional parts: for example, <literal>'1.5 + Internally, <type>interval</type> values are stored as three integral + fields: months, days, and microseconds. These fields are kept + separate because the number of days in a month varies, while a day + can have 23 or 25 hours if a daylight savings time transition is + involved. An interval input string that uses other units is + normalized into this format, and then reconstructed in a standardized + way for output, for example: + +<programlisting> +SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval; + interval +--------------------------------------- + 3 years 3 mons 700 days 133:17:36.789 +</programlisting> + + Here weeks, which are understood as <quote>7 days</quote>, have been + kept separate, while the smaller and larger time units were + combined and normalized. + </para> + + <para> + Input field values can have fractional parts, for example <literal>'1.5 weeks'</literal> or <literal>'01:02:03.45'</literal>. However, - because interval internally stores only three integer units (months, - days, microseconds), fractional units must be spilled to smaller + because <type>interval</type> internally stores only integral fields, + fractional values must be converted into smaller units. Fractional parts of units greater than months are rounded to be an integer number of months, e.g. <literal>'1.5 years'</literal> becomes <literal>'1 year 6 mons'</literal>. Fractional parts of @@ -2903,33 +2924,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>- </tgroup> </table> - <para> - Internally <type>interval</type> 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 <type>timestamp</type> subtraction, - this storage method works well in most cases, but can cause unexpected - results: - -<programlisting> -SELECT EXTRACT(hours from '80 minutes'::interval); - date_part ------------ - 1 - -SELECT EXTRACT(days from '80 hours'::interval); - date_part ------------ - 0 -</programlisting> - - Functions <function>justify_days</function> and - <function>justify_hours</function> are available for adjusting days - and hours that overflow their normal ranges. - </para> - </sect2> <sect2 id="datatype-interval-output"> @@ -2942,6 +2936,23 @@ SELECT EXTRACT(days from '80 hours'::interval); </indexterm> <para> + As previously explained, <productname>PostgreSQL</productname> + stores <type>interval</type> values as months, days, and + microseconds. For output, the months field is converted to years and + months by dividing by 12. The days field is shown as-is. The + microseconds field is converted to hours, minutes, seconds, and + fractional seconds. Thus months, minutes, and seconds will never be + shown as exceeding the ranges 0–11, 0–59, and 0–59 + respectively, while the displayed years, days, and hours fields can + be quite large. (The <link + linkend="function-justify-days"><function>justify_days</function></link> + and <link + linkend="function-justify-hours"><function>justify_hours</function></link> + functions can be used if it is desirable to transpose large days or + hours values into the next higher field.) + </para> + + <para> The output format of the interval type can be set to one of the four styles <literal>sql_standard</literal>, <literal>postgres</literal>, <literal>postgres_verbose</literal>, or <literal>iso_8601</literal>, @@ -4337,7 +4348,7 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' ); <para> The data type <type>uuid</type> stores Universally Unique Identifiers - (UUID) as defined by <ulink url="https://tools.ietf.org/html/rfc4122">RFC 4122</ulink>, + (UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc4122">RFC 4122</ulink>, ISO/IEC 9834-8:2005, and related standards. (Some systems refer to this data type as a globally unique identifier, or GUID,<indexterm><primary>GUID</primary></indexterm> instead.) This |