From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201103111318.p2BDITc09295@momjian.us>
Subject: Re: Missing documentation for uses of extract(field
 from interval)
In-Reply-To: <AANLkTinq_cgsuqgjkM5-R+anvSxTLdG6CD45nsVWCHG4@mail.gmail.com>
To: Daniel Grace <dgrace@wingsnw.com>
Date: Fri, 11 Mar 2011 08:18:29 -0500 (EST)
CC: pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1299849509-10557-5_"
Content-Transfer-Encoding: 7bit


--ELM1299849509-10557-5_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


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. +

--ELM1299849509-10557-5_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/interval.diff"

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>
  

--ELM1299849509-10557-5_--