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

Commitebf52e9

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 parentb992a70 commitebf52e9

File tree

2 files changed

+96
-84
lines changed

2 files changed

+96
-84
lines changed

‎doc/src/sgml/datatype.sgml

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

28102810
<para>
2811-
Field values can have fractional parts: for example, <literal>'1.5
2811+
Internally, <type>interval</type> values are stored as three integral
2812+
fields: months, days, and microseconds. These fields are kept
2813+
separate because the number of days in a month varies, while a day
2814+
can have 23 or 25 hours if a daylight savings time transition is
2815+
involved. An interval input string that uses other units is
2816+
normalized into this format, and then reconstructed in a standardized
2817+
way for output, for example:
2818+
2819+
<programlisting>
2820+
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
2821+
interval
2822+
---------------------------------------
2823+
3 years 3 mons 700 days 133:17:36.789
2824+
</programlisting>
2825+
2826+
Here weeks, which are understood as <quote>7 days</quote>, have been
2827+
kept separate, while the smaller and larger time units were
2828+
combined and normalized.
2829+
</para>
2830+
2831+
<para>
2832+
Input field values can have fractional parts, for example <literal>'1.5
28122833
weeks'</literal> or <literal>'01:02:03.45'</literal>. However,
2813-
because interval internally stores onlythree integer units (months,
2814-
days, microseconds),fractionalunits must bespilled to smaller
2834+
because<type>interval</type> internally stores onlyintegral fields,
2835+
fractionalvalues must beconverted into smaller
28152836
units. Fractional parts of units greater than months are truncated to
28162837
be an integer number of months, e.g. <literal>'1.5 years'</literal>
28172838
becomes <literal>'1 year 6 mons'</literal>. Fractional parts of
@@ -2861,33 +2882,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
28612882
</tgroup>
28622883
</table>
28632884

2864-
<para>
2865-
Internally <type>interval</type> values are stored as months, days,
2866-
and microseconds. This is done because the number of days in a month
2867-
varies, and a day can have 23 or 25 hours if a daylight savings
2868-
time adjustment is involved. The months and days fields are integers
2869-
while the microseconds field can store fractional seconds. Because intervals are
2870-
usually created from constant strings or <type>timestamp</type> subtraction,
2871-
this storage method works well in most cases, but can cause unexpected
2872-
results:
2873-
2874-
<programlisting>
2875-
SELECT EXTRACT(hours from '80 minutes'::interval);
2876-
date_part
2877-
-----------
2878-
1
2879-
2880-
SELECT EXTRACT(days from '80 hours'::interval);
2881-
date_part
2882-
-----------
2883-
0
2884-
</programlisting>
2885-
2886-
Functions <function>justify_days</function> and
2887-
<function>justify_hours</function> are available for adjusting days
2888-
and hours that overflow their normal ranges.
2889-
</para>
2890-
28912885
</sect2>
28922886

28932887
<sect2 id="datatype-interval-output">
@@ -2899,6 +2893,23 @@ SELECT EXTRACT(days from '80 hours'::interval);
28992893
<seealso>formatting</seealso>
29002894
</indexterm>
29012895

2896+
<para>
2897+
As previously explained, <productname>PostgreSQL</productname>
2898+
stores <type>interval</type> values as months, days, and
2899+
microseconds. For output, the months field is converted to years and
2900+
months by dividing by 12. The days field is shown as-is. The
2901+
microseconds field is converted to hours, minutes, seconds, and
2902+
fractional seconds. Thus months, minutes, and seconds will never be
2903+
shown as exceeding the ranges 0&ndash;11, 0&ndash;59, and 0&ndash;59
2904+
respectively, while the displayed years, days, and hours fields can
2905+
be quite large. (The <link
2906+
linkend="function-justify-days"><function>justify_days</function></link>
2907+
and <link
2908+
linkend="function-justify-hours"><function>justify_hours</function></link>
2909+
functions can be used if it is desirable to transpose large days or
2910+
hours values into the next higher field.)
2911+
</para>
2912+
29022913
<para>
29032914
The output format of the interval type can be set to one of the
29042915
four styles <literal>sql_standard</literal>, <literal>postgres</literal>,

‎doc/src/sgml/func.sgml

Lines changed: 55 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -8606,7 +8606,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
86068606
</para>
86078607
<para>
86088608
Subtract timestamps (converting 24-hour intervals into days,
8609-
similarly to <function>justify_hours()</function>)
8609+
similarly to <link
8610+
linkend="function-justify-hours"><function>justify_hours()</function></link>)
86108611
</para>
86118612
<para>
86128613
<literal>timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</literal>
@@ -8975,35 +8976,35 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
89758976

89768977
<row>
89778978
<entry role="func_table_entry"><para role="func_signature">
8978-
<indexterm>
8979+
<indexterm id="function-justify-days">
89798980
<primary>justify_days</primary>
89808981
</indexterm>
89818982
<function>justify_days</function> ( <type>interval</type> )
89828983
<returnvalue>interval</returnvalue>
89838984
</para>
89848985
<para>
8985-
Adjust interval so 30-day time periodsare represented as months
8986+
Adjust interval, converting 30-day time periodsto months
89868987
</para>
89878988
<para>
8988-
<literal>justify_days(interval '35 days')</literal>
8989-
<returnvalue>1mon 5 days</returnvalue>
8989+
<literal>justify_days(interval '1 year 65 days')</literal>
8990+
<returnvalue>1year 2 mons 5 days</returnvalue>
89908991
</para></entry>
89918992
</row>
89928993

89938994
<row>
89948995
<entry role="func_table_entry"><para role="func_signature">
8995-
<indexterm>
8996+
<indexterm id="function-justify-hours">
89968997
<primary>justify_hours</primary>
89978998
</indexterm>
89988999
<function>justify_hours</function> ( <type>interval</type> )
89999000
<returnvalue>interval</returnvalue>
90009001
</para>
90019002
<para>
9002-
Adjust interval so 24-hour time periodsare represented as days
9003+
Adjust interval, converting 24-hour time periodsto days
90039004
</para>
90049005
<para>
9005-
<literal>justify_hours(interval '27 hours')</literal>
9006-
<returnvalue>1 day 03:00:00</returnvalue>
9006+
<literal>justify_hours(interval '50 hours 10 minutes')</literal>
9007+
<returnvalue>2 days 02:10:00</returnvalue>
90079008
</para></entry>
90089009
</row>
90099010

@@ -9426,13 +9427,19 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
94269427
The <function>extract</function> function retrieves subfields
94279428
such as year or hour from date/time values.
94289429
<replaceable>source</replaceable> must be a value expression of
9429-
type <type>timestamp</type>, <type>time</type>,or<type>interval</type>.
9430-
(Expressions of type<type>date</type> are
9431-
cast to <type>timestamp</type> and can therefore be used as
9432-
well.)<replaceable>field</replaceable> is an identifier or
9430+
type <type>timestamp</type>, <type>date</type>, <type>time</type>,
9431+
or<type>interval</type>. (Timestamps and times can be with or
9432+
without time zone.)
9433+
<replaceable>field</replaceable> is an identifier or
94339434
string that selects what field to extract from the source value.
9435+
Not all fields are valid for every input data type; for example, fields
9436+
smaller than a day cannot be extracted from a <type>date</type>, while
9437+
fields of a day or more cannot be extracted from a <type>time</type>.
94349438
The <function>extract</function> function returns values of type
94359439
<type>numeric</type>.
9440+
</para>
9441+
9442+
<para>
94369443
The following are valid field names:
94379444

94389445
<!-- alphabetical -->
@@ -9441,40 +9448,36 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
94419448
<term><literal>century</literal></term>
94429449
<listitem>
94439450
<para>
9444-
The century
9451+
The century; for <type>interval</type> values, the year field
9452+
divided by 100
94459453
</para>
94469454

94479455
<screen>
94489456
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
94499457
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
94509458
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
94519459
<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
9460+
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
9461+
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
9462+
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
9463+
<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
9464+
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
9465+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
94529466
</screen>
9453-
9454-
<para>
9455-
The first century starts at 0001-01-01 00:00:00 AD, although
9456-
they did not know it at the time. This definition applies to all
9457-
Gregorian calendar countries. There is no century number 0,
9458-
you go from -1 century to 1 century.
9459-
9460-
If you disagree with this, please write your complaint to:
9461-
Pope, Cathedral Saint-Peter of Roma, Vatican.
9462-
</para>
94639467
</listitem>
94649468
</varlistentry>
94659469

94669470
<varlistentry>
94679471
<term><literal>day</literal></term>
94689472
<listitem>
94699473
<para>
9470-
For <type>timestamp</type> values, theday(of the month) field
9471-
(1&ndash;31) ; for <type>interval</type>values, the number of days
9474+
Theday of the month (1&ndash;31); for <type>interval</type>
9475+
values, the number of days
94729476
</para>
94739477

94749478
<screen>
94759479
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
94769480
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
9477-
94789481
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
94799482
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
94809483
</screen>
@@ -9548,10 +9551,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
95489551
<screen>
95499552
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
95509553
<lineannotation>Result: </lineannotation><computeroutput>982384720.120000</computeroutput>
9551-
95529554
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
95539555
<lineannotation>Result: </lineannotation><computeroutput>982355920.120000</computeroutput>
9554-
95559556
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
95569557
<lineannotation>Result: </lineannotation><computeroutput>442800.000000</computeroutput>
95579558
</screen>
@@ -9579,7 +9580,8 @@ SELECT to_timestamp(982384720.12);
95799580
<term><literal>hour</literal></term>
95809581
<listitem>
95819582
<para>
9582-
The hour field (0&ndash;23)
9583+
The hour field (0&ndash;23 in timestamps, unrestricted in
9584+
intervals)
95839585
</para>
95849586

95859587
<screen>
@@ -9614,7 +9616,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
96149616
<listitem>
96159617
<para>
96169618
The <acronym>ISO</acronym> 8601 week-numbering year that the date
9617-
falls in (not applicable to intervals)
9619+
falls in
96189620
</para>
96199621

96209622
<screen>
@@ -9631,9 +9633,6 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
96319633
different from the Gregorian year. See the <literal>week</literal>
96329634
field for more information.
96339635
</para>
9634-
<para>
9635-
This field is not available in PostgreSQL releases prior to 8.3.
9636-
</para>
96379636
</listitem>
96389637
</varlistentry>
96399638

@@ -9642,7 +9641,7 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
96429641
<listitem>
96439642
<para>
96449643
The <firstterm>Julian Date</firstterm> corresponding to the
9645-
date or timestamp (not applicable to intervals). Timestamps
9644+
date or timestamp. Timestamps
96469645
that are not local midnight result in a fractional value. See
96479646
<xref linkend="datetime-julian-dates"/> for more information.
96489647
</para>
@@ -9675,12 +9674,15 @@ SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
96759674
<term><literal>millennium</literal></term>
96769675
<listitem>
96779676
<para>
9678-
The millennium
9677+
The millennium; for <type>interval</type> values, the year field
9678+
divided by 1000
96799679
</para>
96809680

96819681
<screen>
96829682
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
96839683
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
9684+
SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
9685+
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
96849686
</screen>
96859687

96869688
<para>
@@ -9723,18 +9725,16 @@ SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
97239725
<term><literal>month</literal></term>
97249726
<listitem>
97259727
<para>
9726-
For <type>timestamp</type> values, thenumber of themonth
9727-
within the year (1&ndash;12) ;for <type>interval</type> values,
9728-
the number of months, modulo 12(0&ndash;11)
9728+
The number of themonth within theyear (1&ndash;12);
9729+
for <type>interval</type> values, the number of months modulo 12
9730+
(0&ndash;11)
97299731
</para>
97309732

97319733
<screen>
97329734
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
97339735
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
9734-
97359736
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
97369737
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
9737-
97389738
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
97399739
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
97409740
</screen>
@@ -9765,7 +9765,6 @@ SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
97659765
<screen>
97669766
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
97679767
<lineannotation>Result: </lineannotation><computeroutput>40.000000</computeroutput>
9768-
97699768
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
97709769
<lineannotation>Result: </lineannotation><computeroutput>28.500000</computeroutput>
97719770
</screen>
@@ -9847,6 +9846,20 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
98479846
</variablelist>
98489847
</para>
98499848

9849+
<para>
9850+
When processing an <type>interval</type> value,
9851+
the <function>extract</function> function produces field values that
9852+
match the interpretation used by the interval output function. This
9853+
can produce surprising results if one starts with a non-normalized
9854+
interval representation, for example:
9855+
<screen>
9856+
SELECT INTERVAL '80 minutes';
9857+
<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
9858+
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
9859+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
9860+
</screen>
9861+
</para>
9862+
98509863
<note>
98519864
<para>
98529865
When the input value is +/-Infinity, <function>extract</function> returns
@@ -9884,7 +9897,6 @@ date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
98849897
<screen>
98859898
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
98869899
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
9887-
98889900
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
98899901
<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
98909902
</screen>
@@ -9962,16 +9974,12 @@ date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [
99629974
<screen>
99639975
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
99649976
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
9965-
99669977
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
99679978
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
9968-
99699979
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
99709980
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
9971-
99729981
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
99739982
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
9974-
99759983
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
99769984
<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
99779985
</screen>
@@ -10010,7 +10018,6 @@ date_bin(<replaceable>stride</replaceable>, <replaceable>source</replaceable>, <
1001010018
<screen>
1001110019
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
1001210020
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput>
10013-
1001410021
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
1001510022
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput>
1001610023
</screen>
@@ -10134,10 +10141,8 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1013410141
<screen>
1013510142
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
1013610143
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
10137-
1013810144
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
1013910145
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
10140-
1014110146
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
1014210147
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
1014310148
</screen>
@@ -10212,16 +10217,12 @@ LOCALTIMESTAMP(<replaceable>precision</replaceable>)
1021210217
<screen>
1021310218
SELECT CURRENT_TIME;
1021410219
<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>
10215-
1021610220
SELECT CURRENT_DATE;
1021710221
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23</computeroutput>
10218-
1021910222
SELECT CURRENT_TIMESTAMP;
1022010223
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522-05</computeroutput>
10221-
1022210224
SELECT CURRENT_TIMESTAMP(2);
1022310225
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.66-05</computeroutput>
10224-
1022510226
SELECT LOCALTIMESTAMP;
1022610227
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522</computeroutput>
1022710228
</screen>

0 commit comments

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