Thread: age() function documentation
I notice that the docs have commented-out all mention of the age()
functions, with the note that "These two functions don't seem to do what
it says here, or anything reasonable at all for that matter."
??
How did we conclude that, and how could these be confusing? I do not
recall any discussion on the topic, and I'll restore the documentation
until someone can refresh my memory on why these are a problem.
<grumble>
- Thomas
> > <grumble> > http://www.postgresql.org/mhonarc/pgsql-hackers/2001-02/msg00550.html OK, so that narrows down the list of suspects ;) Why do you have a problem with the age() function? It *does* behave differently than date subtraction, as explicitly mentioned in the docs (preserving years, etc etc). Would we like some additional clarification in the docs perhaps? Seems to be preferable to dropping all mention, especially since it is a useful function. - Thomas
Thomas Lockhart writes: > I notice that the docs have commented-out all mention of the age() > functions, with the note that "These two functions don't seem to do what > it says here, or anything reasonable at all for that matter." > > ?? > > How did we conclude that, and how could these be confusing? I do not > recall any discussion on the topic, and I'll restore the documentation > until someone can refresh my memory on why these are a problem. > > <grumble> http://www.postgresql.org/mhonarc/pgsql-hackers/2001-02/msg00550.html -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Thomas Lockhart writes: > Why do you have a problem with the age() function? It *does* behave > differently than date subtraction, as explicitly mentioned in the docs > (preserving years, etc etc). As you see in one of the examples I posted, it does not preserve years and months. What exactly does that mean anyway? Simple subtraction also preserves years and months, as I see it. > Would we like some additional clarification in the docs perhaps? Seems > to be preferable to dropping all mention, especially since it is a > useful function. By all means. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> Thomas Lockhart writes: > > > Why do you have a problem with the age() function? It *does* behave > > differently than date subtraction, as explicitly mentioned in the docs > > (preserving years, etc etc). > > As you see in one of the examples I posted, it does not preserve years and > months. What exactly does that mean anyway? Simple subtraction also > preserves years and months, as I see it. From your URL email, this one seems to work: select age(date '1999-05-17', date '1957-06-13'); age------------------------------- 41 years 11 mons 3 days23:00(1 row) This one did not:peter=# select date '1999-08-13' - date '1999-06-13'; ?column?---------- 61(1 row) and this one is less than one month:peter=# select age(date '1999-05-17', date '1999-06-13'); age---------- -27 days(1row) I will admit age() is a little confusing, but it seems to work as intended. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> As you see in one of the examples I posted, it does not preserve years and
> months. What exactly does that mean anyway? Simple subtraction also
> preserves years and months, as I see it.
OK, so there is a documentation problem, since the functions do exactly
what they claim!
What do you mean by "it does not preserve years and months"? I look at
the same example, and run the same example here, and it does exactly
what I would expect, in the way I described it in the docs ;)
> > Would we like some additional clarification in the docs perhaps? Seems
> > to be preferable to dropping all mention, especially since it is a
> > useful function.
> By all means.
OK, I'll add some info. But assuming that we are just missing a clear
definition of what "preserves years and months" means, here it is:
Typical date/time arithmetic resolves to an absolute time or interval.
In those cases, *qualitative* quantities such as years and months are
resolved to a specific absolute interval at the time of calculation.
The age() functions *preserve* the qualitative fields year and month. So
you see the difference in results:
lockhart=# select age('today', '1957-06-13');
-------------------------43 years 9 mons 28 days
lockhart=# select timestamp 'today' - timestamp '1957-06-13';
------------16008 days
In the case for the DATE type, the result is an integer value (not an
interval) which I believe was done intentionally but I'm not recalling
exactly why; I can research it if necessary:
lockhart=# select date 'today' - date '1957-06-13';
---------- 16008
returns the number of days (which is also an absolute, quantitative
time).
- Thomas
Thomas Lockhart wrote: > The age() functions *preserve* the qualitative fields year and month. So > you see the difference in results: Why take away age()? I usually use it to check against INTERVALs? See: village=> select age(date '1999-05-17', date '1957-06-13') > '40 years'::interval;?column? ----------t (1 row) village=> select date '1999-05-17' - date '1957-06-13' > '40 years'::interval;?column? ----------f (1 row) It's useful and I would like to have it this way. -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Thomas Lockhart writes:
> The age() functions *preserve* the qualitative fields year and month. So
> you see the difference in results:
>
> lockhart=# select age('today', '1957-06-13');
> -------------------------
> 43 years 9 mons 28 days
>
> lockhart=# select timestamp 'today' - timestamp '1957-06-13';
> ------------
> 16008 days
>
> In the case for the DATE type, the result is an integer value (not an
> interval) which I believe was done intentionally but I'm not recalling
> exactly why; I can research it if necessary:
>
> lockhart=# select date 'today' - date '1957-06-13';
> ----------
> 16008
>
> returns the number of days (which is also an absolute, quantitative
> time).
ISTM that this is more a result of
a) timestamp subtraction not implemented per spec
b) date substraction not implemented at all (it does date - integer)
c) implicit type conversions running wild
d) intervals not implemented per spec
(spec == SQL). Lots of fun projects here... ;-)
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> ISTM that this is more a result of
> a) timestamp subtraction not implemented per spec
Maybe. But it is implemented consistantly, and is more functional and
capable than the brain-damaged SQL9x spec (c.f. Date and Darwen) asks.
> b) date substraction not implemented at all (it does date - integer)
No, and changing what it *does* do has ramifications.
> c) implicit type conversions running wild
No.
> d) intervals not implemented per spec
? Why would you say this?
> (spec == SQL). Lots of fun projects here... ;-)
SQL == foolishness, sometimes. Especially when it comes to date/time
definitions and arithmetic. But that does not mean that there are things
which could be better, just that a blind conformance to the SQL standard
in this area will fundamentally damage our capabilities, so keep that in
mind.
What issue are you specifically addressing? It is clear that we do not
all have the same understanding of the age() function, but is that a
part of your statements above? Or not??
Please be specific about what you think needs changing, and why. And
I'll actually be able to pay attention after the 7.1 release ;)
- Thomas
Thomas Lockhart writes:
> > b) date substraction not implemented at all (it does date - integer)
>
> No, and changing what it *does* do has ramifications.
Okay, I see there's 'date - date' after all. But 'date - date' should
still return some kind of time interval, not an integer. Of course
changes have ramification, but standing still does, too.
> > d) intervals not implemented per spec
>
> ? Why would you say this?
Because it's a fact. SQL has year to month intervals and day to second
intervals, no all encompassing interval. It sounds stupid at first, but a
lot of weird little definitional problems would go away if we had support
for these. Months and years are "unstable" units when used together with
days, minutes, etc. but they are a consistent system when only used among
themselves. The current implementation already reflects this by making
"time" and "month" different struct members, so I guess what lacks a
little are user-accessible means of controlling which gets used.
The difference is between age() and timestamp subtraction is in fact that
the former returns a year to month interval and the other a day to second
interval. But in the current implementation the only effective difference
is that the interval is diplayed differently, which is a confusing concept
because data values are not defined by their representation but by their
value.
> Please be specific about what you think needs changing, and why. And
> I'll actually be able to pay attention after the 7.1 release ;)
Fix^H^H^HEnhancing the interval type up to spec would really go a long way
I think. We could redefine Interval like
struct Interval { bool is_month_to_year; union { double seconds; struct my { int32 months;
int32 years; } }
}
This would make it mostly compatible to its current behaviour.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Thomas Lockhart writes:
> Typical date/time arithmetic resolves to an absolute time or interval.
> In those cases, *qualitative* quantities such as years and months are
> resolved to a specific absolute interval at the time of calculation.
>
> The age() functions *preserve* the qualitative fields year and month. So
> you see the difference in results:
>
> lockhart=# select age('today', '1957-06-13');
> -------------------------
> 43 years 9 mons 28 days
>
> lockhart=# select timestamp 'today' - timestamp '1957-06-13';
> ------------
> 16008 days
Perhaps age() could be documented along the lines of:
Calculates the difference between the arguments and expresses the
resulting interval in terms of years, months and possibly smaller units.
Ordinary timestamp subtraction is different from age() because it
expresses its result only in days and smaller units.
Plus a contrasting example, such as the above.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/