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

Wed, 11 Apr 2001 15:15:18 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
age() function documentation
> > <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

Wed, 11 Apr 2001 16:09:38 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: age() function documentation
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/


Wed, 11 Apr 2001 18:14:15 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: age() function documentation
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/


Wed, 11 Apr 2001 18:49:45 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: age() function documentation
> 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 days 23: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
	(1 row)

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, Pennsylvania 19026

Wed, 11 Apr 2001 13:16:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: age() function documentation
> 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

Wed, 11 Apr 2001 21:26:21 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: age() function documentation
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

Thu, 12 Apr 2001 10:38:08 +0300
Alessio Bragadini <alessio@albourne.com>
Re: age() function documentation
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/


Thu, 12 Apr 2001 17:47:40 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: age() function documentation
> 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

Thu, 12 Apr 2001 16:07:10 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: age() function documentation
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/


Thu, 12 Apr 2001 19:26:05 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: age() function documentation
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/


Thu, 12 Apr 2001 19:32:40 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: age() function documentation