Re: interval output format available that removes ambiguity ?

On Tue, May 04, 2004 at 22:59:34 +0200,
Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
> 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

It only does this when there is no month to know the length of.
Offhand the only way I know of to get this is to extract the
epoch part of a month which combines the month/year part of the interval
with the week/day/hour/minute/second part without knowing which particular
months are being referred to.

> 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).

No it doesn't do that. In those examples it knows what particular months
are involved and can use the correct length.

> 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.

select '1999-3-2'::date - '1999-2-2'::date;
select '1999-6-2'::date - '1999-5-2'::date;

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

That isn't what I said and that isn't what happens.

>
> 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.

Not exactly. Months are converted to 30 days in the above situation, but
not always.

> Am I missing something here ?

Note that intervals store two different values in them. One is a time in
months and another is in some multiple (possibly 1) of seconds. Often one
or the other of these is zero, but not always.