Missing documentation for uses of extract(field from interval)
(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
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: 16It 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: 16SELECT EXTRACT(DAY FROM INTERVAL '42 days 21:00:00');
Result: 42Likewise 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: 0Though, 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. +