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

Commit6d03e81

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 parent98be940 commit6d03e81

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
@@ -2850,10 +2850,31 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
28502850
</para>
28512851

28522852
<para>
2853-
Field values can have fractional parts: for example, <literal>'1.5
2853+
Internally, <type>interval</type> values are stored as three integral
2854+
fields: months, days, and microseconds. These fields are kept
2855+
separate because the number of days in a month varies, while a day
2856+
can have 23 or 25 hours if a daylight savings time transition is
2857+
involved. An interval input string that uses other units is
2858+
normalized into this format, and then reconstructed in a standardized
2859+
way for output, for example:
2860+
2861+
<programlisting>
2862+
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
2863+
interval
2864+
---------------------------------------
2865+
3 years 3 mons 700 days 133:17:36.789
2866+
</programlisting>
2867+
2868+
Here weeks, which are understood as <quote>7 days</quote>, have been
2869+
kept separate, while the smaller and larger time units were
2870+
combined and normalized.
2871+
</para>
2872+
2873+
<para>
2874+
Input field values can have fractional parts, for example <literal>'1.5
28542875
weeks'</literal> or <literal>'01:02:03.45'</literal>. However,
2855-
because interval internally stores onlythree integer units (months,
2856-
days, microseconds),fractionalunits must bespilled to smaller
2876+
because<type>interval</type> internally stores onlyintegral fields,
2877+
fractionalvalues must beconverted into smaller
28572878
units. Fractional parts of units greater than months are rounded to
28582879
be an integer number of months, e.g. <literal>'1.5 years'</literal>
28592880
becomes <literal>'1 year 6 mons'</literal>. Fractional parts of
@@ -2903,33 +2924,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
29032924
</tgroup>
29042925
</table>
29052926

2906-
<para>
2907-
Internally <type>interval</type> values are stored as months, days,
2908-
and microseconds. This is done because the number of days in a month
2909-
varies, and a day can have 23 or 25 hours if a daylight savings
2910-
time adjustment is involved. The months and days fields are integers
2911-
while the microseconds field can store fractional seconds. Because intervals are
2912-
usually created from constant strings or <type>timestamp</type> subtraction,
2913-
this storage method works well in most cases, but can cause unexpected
2914-
results:
2915-
2916-
<programlisting>
2917-
SELECT EXTRACT(hours from '80 minutes'::interval);
2918-
date_part
2919-
-----------
2920-
1
2921-
2922-
SELECT EXTRACT(days from '80 hours'::interval);
2923-
date_part
2924-
-----------
2925-
0
2926-
</programlisting>
2927-
2928-
Functions <function>justify_days</function> and
2929-
<function>justify_hours</function> are available for adjusting days
2930-
and hours that overflow their normal ranges.
2931-
</para>
2932-
29332927
</sect2>
29342928

29352929
<sect2 id="datatype-interval-output">
@@ -2941,6 +2935,23 @@ SELECT EXTRACT(days from '80 hours'::interval);
29412935
<seealso>formatting</seealso>
29422936
</indexterm>
29432937

2938+
<para>
2939+
As previously explained, <productname>PostgreSQL</productname>
2940+
stores <type>interval</type> values as months, days, and
2941+
microseconds. For output, the months field is converted to years and
2942+
months by dividing by 12. The days field is shown as-is. The
2943+
microseconds field is converted to hours, minutes, seconds, and
2944+
fractional seconds. Thus months, minutes, and seconds will never be
2945+
shown as exceeding the ranges 0&ndash;11, 0&ndash;59, and 0&ndash;59
2946+
respectively, while the displayed years, days, and hours fields can
2947+
be quite large. (The <link
2948+
linkend="function-justify-days"><function>justify_days</function></link>
2949+
and <link
2950+
linkend="function-justify-hours"><function>justify_hours</function></link>
2951+
functions can be used if it is desirable to transpose large days or
2952+
hours values into the next higher field.)
2953+
</para>
2954+
29442955
<para>
29452956
The output format of the interval type can be set to one of the
29462957
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
@@ -9018,7 +9018,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
90189018
</para>
90199019
<para>
90209020
Subtract timestamps (converting 24-hour intervals into days,
9021-
similarly to <function>justify_hours()</function>)
9021+
similarly to <link
9022+
linkend="function-justify-hours"><function>justify_hours()</function></link>)
90229023
</para>
90239024
<para>
90249025
<literal>timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</literal>
@@ -9387,35 +9388,35 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
93879388

93889389
<row>
93899390
<entry role="func_table_entry"><para role="func_signature">
9390-
<indexterm>
9391+
<indexterm id="function-justify-days">
93919392
<primary>justify_days</primary>
93929393
</indexterm>
93939394
<function>justify_days</function> ( <type>interval</type> )
93949395
<returnvalue>interval</returnvalue>
93959396
</para>
93969397
<para>
9397-
Adjust interval so 30-day time periodsare represented as months
9398+
Adjust interval, converting 30-day time periodsto months
93989399
</para>
93999400
<para>
9400-
<literal>justify_days(interval '35 days')</literal>
9401-
<returnvalue>1mon 5 days</returnvalue>
9401+
<literal>justify_days(interval '1 year 65 days')</literal>
9402+
<returnvalue>1year 2 mons 5 days</returnvalue>
94029403
</para></entry>
94039404
</row>
94049405

94059406
<row>
94069407
<entry role="func_table_entry"><para role="func_signature">
9407-
<indexterm>
9408+
<indexterm id="function-justify-hours">
94089409
<primary>justify_hours</primary>
94099410
</indexterm>
94109411
<function>justify_hours</function> ( <type>interval</type> )
94119412
<returnvalue>interval</returnvalue>
94129413
</para>
94139414
<para>
9414-
Adjust interval so 24-hour time periodsare represented as days
9415+
Adjust interval, converting 24-hour time periodsto days
94159416
</para>
94169417
<para>
9417-
<literal>justify_hours(interval '27 hours')</literal>
9418-
<returnvalue>1 day 03:00:00</returnvalue>
9418+
<literal>justify_hours(interval '50 hours 10 minutes')</literal>
9419+
<returnvalue>2 days 02:10:00</returnvalue>
94199420
</para></entry>
94209421
</row>
94219422

@@ -9838,13 +9839,19 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
98389839
The <function>extract</function> function retrieves subfields
98399840
such as year or hour from date/time values.
98409841
<replaceable>source</replaceable> must be a value expression of
9841-
type <type>timestamp</type>, <type>time</type>,or<type>interval</type>.
9842-
(Expressions of type<type>date</type> are
9843-
cast to <type>timestamp</type> and can therefore be used as
9844-
well.)<replaceable>field</replaceable> is an identifier or
9842+
type <type>timestamp</type>, <type>date</type>, <type>time</type>,
9843+
or<type>interval</type>. (Timestamps and times can be with or
9844+
without time zone.)
9845+
<replaceable>field</replaceable> is an identifier or
98459846
string that selects what field to extract from the source value.
9847+
Not all fields are valid for every input data type; for example, fields
9848+
smaller than a day cannot be extracted from a <type>date</type>, while
9849+
fields of a day or more cannot be extracted from a <type>time</type>.
98469850
The <function>extract</function> function returns values of type
98479851
<type>numeric</type>.
9852+
</para>
9853+
9854+
<para>
98489855
The following are valid field names:
98499856

98509857
<!-- alphabetical -->
@@ -9853,40 +9860,36 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
98539860
<term><literal>century</literal></term>
98549861
<listitem>
98559862
<para>
9856-
The century
9863+
The century; for <type>interval</type> values, the year field
9864+
divided by 100
98579865
</para>
98589866

98599867
<screen>
98609868
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
98619869
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
98629870
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
98639871
<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
9872+
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
9873+
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
9874+
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
9875+
<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
9876+
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
9877+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
98649878
</screen>
9865-
9866-
<para>
9867-
The first century starts at 0001-01-01 00:00:00 AD, although
9868-
they did not know it at the time. This definition applies to all
9869-
Gregorian calendar countries. There is no century number 0,
9870-
you go from -1 century to 1 century.
9871-
9872-
If you disagree with this, please write your complaint to:
9873-
Pope, Cathedral Saint-Peter of Roma, Vatican.
9874-
</para>
98759879
</listitem>
98769880
</varlistentry>
98779881

98789882
<varlistentry>
98799883
<term><literal>day</literal></term>
98809884
<listitem>
98819885
<para>
9882-
For <type>timestamp</type> values, theday(of the month) field
9883-
(1&ndash;31) ; for <type>interval</type>values, the number of days
9886+
Theday of the month (1&ndash;31); for <type>interval</type>
9887+
values, the number of days
98849888
</para>
98859889

98869890
<screen>
98879891
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
98889892
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
9889-
98909893
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
98919894
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
98929895
</screen>
@@ -9960,10 +9963,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
99609963
<screen>
99619964
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
99629965
<lineannotation>Result: </lineannotation><computeroutput>982384720.120000</computeroutput>
9963-
99649966
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
99659967
<lineannotation>Result: </lineannotation><computeroutput>982355920.120000</computeroutput>
9966-
99679968
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
99689969
<lineannotation>Result: </lineannotation><computeroutput>442800.000000</computeroutput>
99699970
</screen>
@@ -9991,7 +9992,8 @@ SELECT to_timestamp(982384720.12);
99919992
<term><literal>hour</literal></term>
99929993
<listitem>
99939994
<para>
9994-
The hour field (0&ndash;23)
9995+
The hour field (0&ndash;23 in timestamps, unrestricted in
9996+
intervals)
99959997
</para>
99969998

99979999
<screen>
@@ -10026,7 +10028,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
1002610028
<listitem>
1002710029
<para>
1002810030
The <acronym>ISO</acronym> 8601 week-numbering year that the date
10029-
falls in (not applicable to intervals)
10031+
falls in
1003010032
</para>
1003110033

1003210034
<screen>
@@ -10043,9 +10045,6 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
1004310045
different from the Gregorian year. See the <literal>week</literal>
1004410046
field for more information.
1004510047
</para>
10046-
<para>
10047-
This field is not available in PostgreSQL releases prior to 8.3.
10048-
</para>
1004910048
</listitem>
1005010049
</varlistentry>
1005110050

@@ -10054,7 +10053,7 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
1005410053
<listitem>
1005510054
<para>
1005610055
The <firstterm>Julian Date</firstterm> corresponding to the
10057-
date or timestamp (not applicable to intervals). Timestamps
10056+
date or timestamp. Timestamps
1005810057
that are not local midnight result in a fractional value. See
1005910058
<xref linkend="datetime-julian-dates"/> for more information.
1006010059
</para>
@@ -10087,12 +10086,15 @@ SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
1008710086
<term><literal>millennium</literal></term>
1008810087
<listitem>
1008910088
<para>
10090-
The millennium
10089+
The millennium; for <type>interval</type> values, the year field
10090+
divided by 1000
1009110091
</para>
1009210092

1009310093
<screen>
1009410094
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
1009510095
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
10096+
SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
10097+
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
1009610098
</screen>
1009710099

1009810100
<para>
@@ -10135,18 +10137,16 @@ SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
1013510137
<term><literal>month</literal></term>
1013610138
<listitem>
1013710139
<para>
10138-
For <type>timestamp</type> values, thenumber of themonth
10139-
within the year (1&ndash;12) ;for <type>interval</type> values,
10140-
the number of months, modulo 12(0&ndash;11)
10140+
The number of themonth within theyear (1&ndash;12);
10141+
for <type>interval</type> values, the number of months modulo 12
10142+
(0&ndash;11)
1014110143
</para>
1014210144

1014310145
<screen>
1014410146
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
1014510147
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
10146-
1014710148
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
1014810149
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
10149-
1015010150
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
1015110151
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
1015210152
</screen>
@@ -10177,7 +10177,6 @@ SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
1017710177
<screen>
1017810178
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
1017910179
<lineannotation>Result: </lineannotation><computeroutput>40.000000</computeroutput>
10180-
1018110180
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
1018210181
<lineannotation>Result: </lineannotation><computeroutput>28.500000</computeroutput>
1018310182
</screen>
@@ -10259,6 +10258,20 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
1025910258
</variablelist>
1026010259
</para>
1026110260

10261+
<para>
10262+
When processing an <type>interval</type> value,
10263+
the <function>extract</function> function produces field values that
10264+
match the interpretation used by the interval output function. This
10265+
can produce surprising results if one starts with a non-normalized
10266+
interval representation, for example:
10267+
<screen>
10268+
SELECT INTERVAL '80 minutes';
10269+
<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
10270+
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
10271+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
10272+
</screen>
10273+
</para>
10274+
1026210275
<note>
1026310276
<para>
1026410277
When the input value is +/-Infinity, <function>extract</function> returns
@@ -10296,7 +10309,6 @@ date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
1029610309
<screen>
1029710310
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
1029810311
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
10299-
1030010312
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
1030110313
<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
1030210314
</screen>
@@ -10374,16 +10386,12 @@ date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [
1037410386
<screen>
1037510387
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
1037610388
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
10377-
1037810389
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
1037910390
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
10380-
1038110391
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
1038210392
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
10383-
1038410393
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
1038510394
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
10386-
1038710395
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
1038810396
<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
1038910397
</screen>
@@ -10422,7 +10430,6 @@ date_bin(<replaceable>stride</replaceable>, <replaceable>source</replaceable>, <
1042210430
<screen>
1042310431
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
1042410432
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput>
10425-
1042610433
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
1042710434
<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput>
1042810435
</screen>
@@ -10546,10 +10553,8 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1054610553
<screen>
1054710554
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
1054810555
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
10549-
1055010556
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
1055110557
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
10552-
1055310558
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
1055410559
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
1055510560
</screen>
@@ -10624,16 +10629,12 @@ LOCALTIMESTAMP(<replaceable>precision</replaceable>)
1062410629
<screen>
1062510630
SELECT CURRENT_TIME;
1062610631
<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>
10627-
1062810632
SELECT CURRENT_DATE;
1062910633
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23</computeroutput>
10630-
1063110634
SELECT CURRENT_TIMESTAMP;
1063210635
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522-05</computeroutput>
10633-
1063410636
SELECT CURRENT_TIMESTAMP(2);
1063510637
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.66-05</computeroutput>
10636-
1063710638
SELECT LOCALTIMESTAMP;
1063810639
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522</computeroutput>
1063910640
</screen>

0 commit comments

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