pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedMissing documentation for uses of extract(field from interval)
2+ messages / 2 participants
[nested] [flat]
* Missing documentation for uses of extract(field from interval)
@ 2011-02-08 21:30 Daniel Grace <dgrace@wingsnw.com>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Grace @ 2011-02-08 21:30 UTC (permalink / raw)
To: pgsql-docs
(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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Missing documentation for uses of extract(field from interval)
@ 2011-03-11 13:18 Bruce Momjian <bruce@momjian.us>
parent: Daniel Grace <dgrace@wingsnw.com>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2011-03-11 13:18 UTC (permalink / raw)
To: Daniel Grace <dgrace@wingsnw.com>; +Cc: 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. +
Attachments:
/rtmp/interval.diff (text/x-diff, 1.8K) download
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>
end of thread, other threads:[~2011-03-11 13:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-08 21:30 Missing documentation for uses of extract(field from interval) Daniel Grace <dgrace@wingsnw.com>
2011-03-11 13:18 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox