Re: interval output format available that removes ambiguity ? - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: interval output format available that removes ambiguity ?
Date
Msg-id 20040504225934.A619@hermes.hilbert.loc
Whole thread Raw
In response to Re: interval output format available that removes ambiguity ?  (Bruno Wolff III <bruno@wolff.to>)
Responses Re: interval output format available that removes ambiguity ?
Re: interval output format available that removes ambiguity ?
List pgsql-general
Bruno,

thanks for answering. I still have some questions:

> > I have the need to output intervals (ages in this case).
> > PostgreSQL takes great care to handle months correctly (eg
> > take into account varying months lengths). This is only
> > possible if either end point or start point of an interval are
> > known. For post processing some of the ambiguity of what
> > "2 mons" means would be removed if "61 days" was returned.
>
> This is sort of done now, but the months part of the interval will be
> treated as 30 days.
Are you saying that when PostgreSQL returns "... 3 mons ..."
as a representation of an interval I can safely assume that
when it calculated the number of months it used 30 days
regardless of the actual length of the month ? I couldn't find
that number mentioned anywhere and had not browsed the source
yet. That would also be contrary to what I thought. I assumed
the following would happen:

 select age('1999-2-2', '1999-3-2');
 select age('1999-5-2', '1999-6-2');

would both return "1 mon" (despite the first one being 28 days
and the second one being 31 days).

I am now looking for a way to say:

 select age('1999-2-2', '1999-3-2', without months);
 select age('1999-5-2', '1999-6-2', without months);

and get "28 days" in the first and "31 days" in the second
result.

However, if you say that "1 mon" is always considered 30 days
in this context I would expect to receive:

1) "1 mon -2 days"  (it would return 28 days of course, I know)
2) "1 mon 1 day"

Neither 7.1 nor 7.4 return that.

> You can extract "epoch" from the interval to get the total number of
> seconds in the interval (converting months to the number of seconds
> in 30 days) and then divide that by the appropiate amount.
That only works if the above holds true, eg the month must be
fixed to 30 days by the calculation *generating* the interval
representation. Applying epoch *after* the fact is no good,
does it, because the epoch() code won't know whether "1 mons"
is to be 28 or 29 or 30 or 31 days.

Am I missing something here ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

pgsql-general by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: Variable in PostgreSQL 7.4.x
Next
From: Bruno Wolff III
Date:
Subject: Re: interval output format available that removes ambiguity ?