interval output format available that removes ambiguity ?

On Tue, May 04, 2004 at 12:24:37 +0200,
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

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.

Is there a way to tell PostgreSQL to return that type of
interval (eg use weeks, days, hours, minutes, seconds, ...
but not months and perhaps not even years [leap years, etc]) ?
to_char(interval, text) doesn't work as it is applied after
the fact.

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.

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

On Tue, May 04, 2004 at 22:59:34 +0200,
Karsten Hilbert <Karsten.Hilbert@gmx.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.

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

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.

Just subtract the two timestamps (or dates) instead of using age().
Then you get an interval that has no month component.

However, if you say that "1 mon" is always considered 30 days

He didn't say that. He said that when the system *must* convert a
month-based interval to days and it has no date reference for it,
it uses 30 days. Something like "now() + '1 month'::interval"
will do the "right thing". This IMHO is the main application of
intervals with month components ...

regards, tom lane

Tom,

Just subtract the two timestamps (or dates) instead of using age().
Then you get an interval that has no month component.

*That* was what I was looking for. Thanks !

He didn't say that. He said that when the system *must* convert a
month-based interval to days and it has no date reference for it,
it uses 30 days. Something like "now() + '1 month'::interval"
will do the "right thing". This IMHO is the main application of
intervals with month components ...

I knew PostgreSQL would do the Right Thing(tm) where possible
and assume reasonable defaults where ambiguity exists. I just
didn't know how to tell it to return the right version of the
Right Thing.

As usual, sage advice.

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