Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfcd210d

Browse files
committed
Doc: improve explanation of type interval, especially extract().
The explanation of interval's behavior in datatype.sgml wasn't wrongexactly, but it was unclear, partly because it buried the lede aboutthere being three internal fields. Rearrange and wordsmith for moreclarity.The discussion of extract() claimed that input of type date washandled by casting, but actually there's been a separate SQL functiontaking date for a very long time. Also, it was mostly silent abouthow interval inputs are handled, but there are several field typesfor which it seems useful to be specific.Improve discussion of justify_days()/justify_hours() too.In passing, remove vertical space in some groups of examples,as there was little consistency about whether to have such spaceor not. (I only did this within the datetime functions section;there are some related inconsistencies elsewhere.)Per discussion of bug #18348 from Michael Bondarenko. Theremay be some code changes coming out of that discussion too,but we likely won't back-patch them. This docs-only patchseems useful to back-patch, though I only carried it back tov13 because it didn't apply easily in v12.Discussion:https://postgr.es/m/18348-b097a3587dfde8a4@postgresql.org
1 parent489072a commitfcd210d

File tree

2 files changed

+96
-86
lines changed

2 files changed

+96
-86
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 41 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -2869,10 +2869,31 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
28692869
</para>
28702870

28712871
<para>
2872-
Field values can have fractional parts: for example, <literal>'1.5
2872+
Internally, <type>interval</type> values are stored as three integral
2873+
fields: months, days, and microseconds. These fields are kept
2874+
separate because the number of days in a month varies, while a day
2875+
can have 23 or 25 hours if a daylight savings time transition is
2876+
involved. An interval input string that uses other units is
2877+
normalized into this format, and then reconstructed in a standardized
2878+
way for output, for example:
2879+
2880+
<programlisting>
2881+
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
2882+
interval
2883+
---------------------------------------
2884+
3 years 3 mons 700 days 133:17:36.789
2885+
</programlisting>
2886+
2887+
Here weeks, which are understood as <quote>7 days</quote>, have been
2888+
kept separate, while the smaller and larger time units were
2889+
combined and normalized.
2890+
</para>
2891+
2892+
<para>
2893+
Input field values can have fractional parts, for example <literal>'1.5
28732894
weeks'</literal> or <literal>'01:02:03.45'</literal>. However,
2874-
because interval internally stores onlythree integer units (months,
2875-
days, microseconds),fractionalunits must bespilled to smaller
2895+
because<type>interval</type> internally stores onlyintegral fields,
2896+
fractionalvalues must beconverted into smaller
28762897
units. Fractional parts of units greater than months are rounded to
28772898
be an integer number of months, e.g. <literal>'1.5 years'</literal>
28782899
becomes <literal>'1 year 6 mons'</literal>. Fractional parts of
@@ -2922,33 +2943,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
29222943
</tgroup>
29232944
</table>
29242945

2925-
<para>
2926-
Internally <type>interval</type> values are stored as months, days,
2927-
and microseconds. This is done because the number of days in a month
2928-
varies, and a day can have 23 or 25 hours if a daylight savings
2929-
time adjustment is involved. The months and days fields are integers
2930-
while the microseconds field can store fractional seconds. Because intervals are
2931-
usually created from constant strings or <type>timestamp</type> subtraction,
2932-
this storage method works well in most cases, but can cause unexpected
2933-
results:
2934-
2935-
<programlisting>
2936-
SELECT EXTRACT(hours from '80 minutes'::interval);
2937-
date_part
2938-
-----------
2939-
1
2940-
2941-
SELECT EXTRACT(days from '80 hours'::interval);
2942-
date_part
2943-
-----------
2944-
0
2945-
</programlisting>
2946-
2947-
Functions <function>justify_days</function> and
2948-
<function>justify_hours</function> are available for adjusting days
2949-
and hours that overflow their normal ranges.
2950-
</para>
2951-
29522946
</sect2>
29532947

29542948
<sect2 id="datatype-interval-output">
@@ -2960,6 +2954,23 @@ SELECT EXTRACT(days from '80 hours'::interval);
29602954
<seealso>formatting</seealso>
29612955
</indexterm>
29622956

2957+
<para>
2958+
As previously explained, <productname>PostgreSQL</productname>
2959+
stores <type>interval</type> values as months, days, and
2960+
microseconds. For output, the months field is converted to years and
2961+
months by dividing by 12. The days field is shown as-is. The
2962+
microseconds field is converted to hours, minutes, seconds, and
2963+
fractional seconds. Thus months, minutes, and seconds will never be
2964+
shown as exceeding the ranges 0&ndash;11, 0&ndash;59, and 0&ndash;59
2965+
respectively, while the displayed years, days, and hours fields can
2966+
be quite large. (The <link
2967+
linkend="function-justify-days"><function>justify_days</function></link>
2968+
and <link
2969+
linkend="function-justify-hours"><function>justify_hours</function></link>
2970+
functions can be used if it is desirable to transpose large days or
2971+
hours values into the next higher field.)
2972+
</para>
2973+
29632974
<para>
29642975
The output format of the interval type can be set to one of the
29652976
four styles <literal>sql_standard</literal>, <literal>postgres</literal>,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp