BUG #4878: function age() give a wrong interval

select
age(
'2009-06-23 18:36:05.064066+02' ,
'2009-05-12 18:36:05.064066+02') ;

Result: "1 mon 11 days"

select justify_interval('1000 hours');

Result: "1 mon 11 days 16:00:00"

select
age(
'2009-06-23 18:36:05.064066+02' ,
'2009-05-12 18:36:05.064066+02')

interval '1000 hours'

;

Result: false

And that's correct, 1 month and 11 days is less than 1 month, 11 days
and 16 hours, it's not more. This is the actual comparison:

select interval '1 mon 11 day' > interval '1 mon 11 day 16 hour';

I don't see a problem nor a bug.

Regards,
Frank

Op 25 jun 2009, om 11:28 heeft pamelant@companeo.com het volgende
geschreven:

Le jeudi 25 juin 2009 à 11:40 +0200, Frank Heikens a écrit :

select
age(
'2009-06-23 18:36:05.064066+02' ,
'2009-05-12 18:36:05.064066+02') ;

Result: "1 mon 11 days"

select justify_interval('1000 hours');

Result: "1 mon 11 days 16:00:00"

select
age(
'2009-06-23 18:36:05.064066+02' ,
'2009-05-12 18:36:05.064066+02')

interval '1000 hours'

;

Result: false

And that's correct, 1 month and 11 days is less than 1 month, 11 days
and 16 hours, it's not more. This is the actual comparison:

select interval '1 mon 11 day' > interval '1 mon 11 day 16 hour';

I don't see a problem nor a bug.

this is wrong because first interval is 1008 hour and the second is 1000
hours

In the first case you have a month with 31 days and in the second you
have a month with 30 days

try this

select
age(current_timestamp , current_timestamp - '1008 hours'::interval)

interval '1000 hours'

so 1000 > 1008

regards

The problem is the definition of a month. That can be 28, 29, 30 or 31
days. This is what the manual says about age():
age(timestamp, timestamp)
interval
Subtract arguments, producing a "symbolic" result that uses years and
months

So, it's just a symbolic age, not an exact age. The same occurs with
years, a year can be 365 days or 366 days. And there are also issues
with extra seconds and summer and wintertime.

time === trouble

Regards,
Frank

Op 25 jun 2009, om 12:50 heeft Philippe Amelant het volgende geschreven:

"Philippe Amelant" <pamelant@companeo.com> writes:

In the first case you have a month with 31 days and in the second you
have a month with 30 days

The interval comparisons have no way to know that, so they arbitrarily
assume that '1 month' is equivalent to '30 days'. This isn't going to
be changed. If you don't like it, don't use age(). A plain old
timestamp subtraction will probably provide behavior that's closer to
what you want.

regards, tom lane