Re: Missing documentation for uses of extract(field from interval)
От | Bruce Momjian |
---|---|
Тема | Re: Missing documentation for uses of extract(field from interval) |
Дата | |
Msg-id | 201103111318.p2BDITc09295@momjian.us обсуждение исходный текст |
Ответ на | Missing documentation for uses of extract(field from interval) (Daniel Grace <dgrace@wingsnw.com>) |
Список | pgsql-docs |
I have added documentation for EXTRACT(DAY) usage with intervals. We had something for MONTH, but for some reason nothing for DAY. Applied patch attached. --------------------------------------------------------------------------- Daniel Grace wrote: > (Note: I'm not subscribed to pgsql-docs, so please CC me on any > replies. Thanks!) > > I pulled up the documentation today to see if I could get just the > number of days in an interval using EXTRACT. The answer provided was > vague: While you can use EXTRACT(field from interval) (shown in table > 9-27), section 9.9.1 only lists: > > day > > The day (of the month) field (1 - 31) > > SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); > Result: 16 > > > > It turns out that EXTRACT(day FROM interval) does work as expected -- > so perhaps the documentation should be updated to something like: > > day > > The day (of the month) field (1 - 31), or the number of days in the interval > > SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); > Result: 16 > > SELECT EXTRACT(DAY FROM INTERVAL '42 days 21:00:00'); > Result: 42 > > Likewise for month, year, century and maybe some others. (hour, > minute, and *second{s?} probably don't need updated text). > > There also should probably be some warning in here about EXTRACT on > intervals however. Namely cases like this: > > SELECT EXTRACT(YEAR FROM INTERVAL '400 days') --> Result: 0 > (because the interval is '400 days' not '1 year 35 days') > SELECT EXTRACT(DAY FROM INTERVAL '1 year') --> Result: 0 (because > the interval is '1 year' not '365 days') > SELECT EXTRACT(DAY FROM INTERVAL '1 month') --> Result: 0 > > Though, this works: > SELECT EXTRACT(CENTURY FROM INTERVAL '99 years') --> Result: 0 > SELECT EXTRACT(CENTURY FROM INTERVAL '100 years') --> Result: 1 > -- > Daniel Grace > AGE, LLC > System Administrator and Software Developer > > -- > Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-docs -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 4635cf2..c392109 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** SELECT EXTRACT(CENTURY FROM TIMESTAMP '2 *** 6605,6617 **** <term><literal>day</literal></term> <listitem> <para> ! The day (of the month) field (1 - 31) </para> <screen> SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput> </screen> </listitem> </varlistentry> --- 6605,6624 ---- <term><literal>day</literal></term> <listitem> <para> ! For <type>timestamp</type> values, the day (of the month) field ! (1 - 31) ; for <type>interval</type> values, the number of days </para> <screen> SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput> + + SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); + <lineannotation>Result: </lineannotation><computeroutput>40</computeroutput> </screen> + + + </listitem> </varlistentry> *************** SELECT EXTRACT(MINUTE FROM TIMESTAMP '20 *** 6827,6833 **** <listitem> <para> For <type>timestamp</type> values, the number of the month ! within the year (1 - 12) ; for <type>interval</type> values the number of months, modulo 12 (0 - 11) </para> --- 6834,6840 ---- <listitem> <para> For <type>timestamp</type> values, the number of the month ! within the year (1 - 12) ; for <type>interval</type> values, the number of months, modulo 12 (0 - 11) </para>
В списке pgsql-docs по дате отправления: