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

Commiteb1d008

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 parent3850fcc commiteb1d008

File tree

2 files changed

+96
-83
lines changed

2 files changed

+96
-83
lines changed

‎doc/src/sgml/datatype.sgml

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

27662766
<para>
2767-
Field values can have fractional parts: for example, <literal>'1.5
2767+
Internally, <type>interval</type> values are stored as three integral
2768+
fields: months, days, and microseconds. These fields are kept
2769+
separate because the number of days in a month varies, while a day
2770+
can have 23 or 25 hours if a daylight savings time transition is
2771+
involved. An interval input string that uses other units is
2772+
normalized into this format, and then reconstructed in a standardized
2773+
way for output, for example:
2774+
2775+
<programlisting>
2776+
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
2777+
interval
2778+
---------------------------------------
2779+
3 years 3 mons 700 days 133:17:36.789
2780+
</programlisting>
2781+
2782+
Here weeks, which are understood as <quote>7 days</quote>, have been
2783+
kept separate, while the smaller and larger time units were
2784+
combined and normalized.
2785+
</para>
2786+
2787+
<para>
2788+
Input field values can have fractional parts, for example <literal>'1.5
27682789
weeks'</literal> or <literal>'01:02:03.45'</literal>. However,
2769-
because interval internally stores onlythree integer units (months,
2770-
days, microseconds),fractionalunits must bespilled to smaller
2790+
because<type>interval</type> internally stores onlyintegral fields,
2791+
fractionalvalues must beconverted into smaller
27712792
units. Fractional parts of units greater than months are truncated to
27722793
be an integer number of months, e.g. <literal>'1.5 years'</literal>
27732794
becomes <literal>'1 year 6 mons'</literal>. Fractional parts of
@@ -2817,33 +2838,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
28172838
</tgroup>
28182839
</table>
28192840

2820-
<para>
2821-
Internally <type>interval</type> values are stored as months, days,
2822-
and microseconds. This is done because the number of days in a month
2823-
varies, and a day can have 23 or 25 hours if a daylight savings
2824-
time adjustment is involved. The months and days fields are integers
2825-
while the microseconds field can store fractional seconds. Because intervals are
2826-
usually created from constant strings or <type>timestamp</type> subtraction,
2827-
this storage method works well in most cases, but can cause unexpected
2828-
results:
2829-
2830-
<programlisting>
2831-
SELECT EXTRACT(hours from '80 minutes'::interval);
2832-
date_part
2833-
-----------
2834-
1
2835-
2836-
SELECT EXTRACT(days from '80 hours'::interval);
2837-
date_part
2838-
-----------
2839-
0
2840-
</programlisting>
2841-
2842-
Functions <function>justify_days</function> and
2843-
<function>justify_hours</function> are available for adjusting days
2844-
and hours that overflow their normal ranges.
2845-
</para>
2846-
28472841
</sect2>
28482842

28492843
<sect2 id="datatype-interval-output">
@@ -2855,6 +2849,23 @@ SELECT EXTRACT(days from '80 hours'::interval);
28552849
<seealso>formatting</seealso>
28562850
</indexterm>
28572851

2852+
<para>
2853+
As previously explained, <productname>PostgreSQL</productname>
2854+
stores <type>interval</type> values as months, days, and
2855+
microseconds. For output, the months field is converted to years and
2856+
months by dividing by 12. The days field is shown as-is. The
2857+
microseconds field is converted to hours, minutes, seconds, and
2858+
fractional seconds. Thus months, minutes, and seconds will never be
2859+
shown as exceeding the ranges 0&ndash;11, 0&ndash;59, and 0&ndash;59
2860+
respectively, while the displayed years, days, and hours fields can
2861+
be quite large. (The <link
2862+
linkend="function-justify-days"><function>justify_days</function></link>
2863+
and <link
2864+
linkend="function-justify-hours"><function>justify_hours</function></link>
2865+
functions can be used if it is desirable to transpose large days or
2866+
hours values into the next higher field.)
2867+
</para>
2868+
28582869
<para>
28592870
The output format of the interval type can be set to one of the
28602871
four styles <literal>sql_standard</literal>, <literal>postgres</literal>,

‎doc/src/sgml/func.sgml

Lines changed: 55 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -8471,7 +8471,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
84718471
</para>
84728472
<para>
84738473
Subtract timestamps (converting 24-hour intervals into days,
8474-
similarly to <function>justify_hours()</function>)
8474+
similarly to <link
8475+
linkend="function-justify-hours"><function>justify_hours()</function></link>)
84758476
</para>
84768477
<para>
84778478
<literal>timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</literal>
@@ -8826,35 +8827,35 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
88268827

88278828
<row>
88288829
<entry role="func_table_entry"><para role="func_signature">
8829-
<indexterm>
8830+
<indexterm id="function-justify-days">
88308831
<primary>justify_days</primary>
88318832
</indexterm>
88328833
<function>justify_days</function> ( <type>interval</type> )
88338834
<returnvalue>interval</returnvalue>
88348835
</para>
88358836
<para>
8836-
Adjust interval so 30-day time periodsare represented as months
8837+
Adjust interval, converting 30-day time periodsto months
88378838
</para>
88388839
<para>
8839-
<literal>justify_days(interval '35 days')</literal>
8840-
<returnvalue>1mon 5 days</returnvalue>
8840+
<literal>justify_days(interval '1 year 65 days')</literal>
8841+
<returnvalue>1year 2 mons 5 days</returnvalue>
88418842
</para></entry>
88428843
</row>
88438844

88448845
<row>
88458846
<entry role="func_table_entry"><para role="func_signature">
8846-
<indexterm>
8847+
<indexterm id="function-justify-hours">
88478848
<primary>justify_hours</primary>
88488849
</indexterm>
88498850
<function>justify_hours</function> ( <type>interval</type> )
88508851
<returnvalue>interval</returnvalue>
88518852
</para>
88528853
<para>
8853-
Adjust interval so 24-hour time periodsare represented as days
8854+
Adjust interval, converting 24-hour time periodsto days
88548855
</para>
88558856
<para>
8856-
<literal>justify_hours(interval '27 hours')</literal>
8857-
<returnvalue>1 day 03:00:00</returnvalue>
8857+
<literal>justify_hours(interval '50 hours 10 minutes')</literal>
8858+
<returnvalue>2 days 02:10:00</returnvalue>
88588859
</para></entry>
88598860
</row>
88608861

@@ -9269,13 +9270,19 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
92699270
The <function>extract</function> function retrieves subfields
92709271
such as year or hour from date/time values.
92719272
<replaceable>source</replaceable> must be a value expression of
9272-
type <type>timestamp</type>, <type>time</type>,or<type>interval</type>.
9273-
(Expressions of type<type>date</type> are
9274-
cast to <type>timestamp</type> and can therefore be used as
9275-
well.)<replaceable>field</replaceable> is an identifier or
9273+
type <type>timestamp</type>, <type>date</type>, <type>time</type>,
9274+
or<type>interval</type>. (Timestamps and times can be with or
9275+
without time zone.)
9276+
<replaceable>field</replaceable> is an identifier or
92769277
string that selects what field to extract from the source value.
9278+
Not all fields are valid for every input data type; for example, fields
9279+
smaller than a day cannot be extracted from a <type>date</type>, while
9280+
fields of a day or more cannot be extracted from a <type>time</type>.
92779281
The <function>extract</function> function returns values of type
92789282
<type>double precision</type>.
9283+
</para>
9284+
9285+
<para>
92799286
The following are valid field names:
92809287

92819288
<!-- alphabetical -->
@@ -9284,40 +9291,36 @@ EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
92849291
<term><literal>century</literal></term>
92859292
<listitem>
92869293
<para>
9287-
The century
9294+
The century; for <type>interval</type> values, the year field
9295+
divided by 100
92889296
</para>
92899297

92909298
<screen>
92919299
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
92929300
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
92939301
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
92949302
<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
9303+
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
9304+
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
9305+
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
9306+
<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
9307+
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
9308+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
92959309
</screen>
9296-
9297-
<para>
9298-
The first century starts at 0001-01-01 00:00:00 AD, although
9299-
they did not know it at the time. This definition applies to all
9300-
Gregorian calendar countries. There is no century number 0,
9301-
you go from -1 century to 1 century.
9302-
9303-
If you disagree with this, please write your complaint to:
9304-
Pope, Cathedral Saint-Peter of Roma, Vatican.
9305-
</para>
93069310
</listitem>
93079311
</varlistentry>
93089312

93099313
<varlistentry>
93109314
<term><literal>day</literal></term>
93119315
<listitem>
93129316
<para>
9313-
For <type>timestamp</type> values, theday(of the month) field
9314-
(1&ndash;31) ; for <type>interval</type>values, the number of days
9317+
Theday of the month (1&ndash;31); for <type>interval</type>
9318+
values, the number of days
93159319
</para>
93169320

93179321
<screen>
93189322
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
93199323
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
9320-
93219324
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
93229325
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
93239326
</screen>
@@ -9391,10 +9394,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
93919394
<screen>
93929395
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
93939396
<lineannotation>Result: </lineannotation><computeroutput>982384720.12</computeroutput>
9394-
93959397
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
93969398
<lineannotation>Result: </lineannotation><computeroutput>982355920.12</computeroutput>
9397-
93989399
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
93999400
<lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
94009401
</screen>
@@ -9422,7 +9423,8 @@ SELECT to_timestamp(982384720.12);
94229423
<term><literal>hour</literal></term>
94239424
<listitem>
94249425
<para>
9425-
The hour field (0&ndash;23)
9426+
The hour field (0&ndash;23 in timestamps, unrestricted in
9427+
intervals)
94269428
</para>
94279429

94289430
<screen>
@@ -9457,7 +9459,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
94579459
<listitem>
94589460
<para>
94599461
The <acronym>ISO</acronym> 8601 week-numbering year that the date
9460-
falls in (not applicable to intervals)
9462+
falls in
94619463
</para>
94629464

94639465
<screen>
@@ -9474,9 +9476,6 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
94749476
different from the Gregorian year. See the <literal>week</literal>
94759477
field for more information.
94769478
</para>
9477-
<para>
9478-
This field is not available in PostgreSQL releases prior to 8.3.
9479-
</para>
94809479
</listitem>
94819480
</varlistentry>
94829481

@@ -9485,7 +9484,7 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
94859484
<listitem>
94869485
<para>
94879486
The <firstterm>Julian Date</firstterm> corresponding to the
9488-
date or timestamp (not applicable to intervals). Timestamps
9487+
date or timestamp. Timestamps
94899488
that are not local midnight result in a fractional value. See
94909489
<xref linkend="datetime-julian-dates"/> for more information.
94919490
</para>
@@ -9518,12 +9517,15 @@ SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
95189517
<term><literal>millennium</literal></term>
95199518
<listitem>
95209519
<para>
9521-
The millennium
9520+
The millennium; for <type>interval</type> values, the year field
9521+
divided by 1000
95229522
</para>
95239523

95249524
<screen>
95259525
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
95269526
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
9527+
SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
9528+
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
95279529
</screen>
95289530

95299531
<para>
@@ -9566,18 +9568,16 @@ SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
95669568
<term><literal>month</literal></term>
95679569
<listitem>
95689570
<para>
9569-
For <type>timestamp</type> values, thenumber of themonth
9570-
within the year (1&ndash;12) ;for <type>interval</type> values,
9571-
the number of months, modulo 12(0&ndash;11)
9571+
The number of themonth within theyear (1&ndash;12);
9572+
for <type>interval</type> values, the number of months modulo 12
9573+
(0&ndash;11)
95729574
</para>
95739575

95749576
<screen>
95759577
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
95769578
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
9577-
95789579
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
95799580
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
9580-
95819581
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
95829582
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
95839583
</screen>
@@ -9608,7 +9608,6 @@ SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
96089608
<screen>
96099609
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
96109610
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
9611-
96129611
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
96139612
<lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
96149613
</screen>
@@ -9690,6 +9689,20 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
96909689
</variablelist>
96919690
</para>
96929691

9692+
<para>
9693+
When processing an <type>interval</type> value,
9694+
the <function>extract</function> function produces field values that
9695+
match the interpretation used by the interval output function. This
9696+
can produce surprising results if one starts with a non-normalized
9697+
interval representation, for example:
9698+
<screen>
9699+
SELECT INTERVAL '80 minutes';
9700+
<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
9701+
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
9702+
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
9703+
</screen>
9704+
</para>
9705+
96939706
<note>
96949707
<para>
96959708
When the input value is +/-Infinity, <function>extract</function> returns
@@ -9723,7 +9736,6 @@ date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
97239736
<screen>
97249737
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
97259738
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
9726-
97279739
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
97289740
<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
97299741
</screen>
@@ -9801,16 +9813,12 @@ date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [
98019813
<screen>
98029814
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
98039815
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
9804-
98059816
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
98069817
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
9807-
98089818
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
98099819
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
9810-
98119820
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
98129821
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
9813-
98149822
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
98159823
<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
98169824
</screen>
@@ -9923,10 +9931,8 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
99239931
<screen>
99249932
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
99259933
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
9926-
99279934
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
99289935
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
9929-
99309936
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
99319937
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
99329938
</screen>
@@ -10001,16 +10007,12 @@ LOCALTIMESTAMP(<replaceable>precision</replaceable>)
1000110007
<screen>
1000210008
SELECT CURRENT_TIME;
1000310009
<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>
10004-
1000510010
SELECT CURRENT_DATE;
1000610011
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23</computeroutput>
10007-
1000810012
SELECT CURRENT_TIMESTAMP;
1000910013
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522-05</computeroutput>
10010-
1001110014
SELECT CURRENT_TIMESTAMP(2);
1001210015
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.66-05</computeroutput>
10013-
1001410016
SELECT LOCALTIMESTAMP;
1001510017
<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522</computeroutput>
1001610018
</screen>

0 commit comments

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