Обсуждение: BUG #4878: function age() give a wrong interval

The following bug has been logged online:

Bug reference:      4878
Logged by:
Email address:      pamelant@companeo.com
PostgreSQL version: 8.2.4, 8.3.6
Operating system:   linux
Description:        function age() give a wrong interval
Details:

age() report a wrong interval in some case

example

intervall between the 2 dates is 1008 hours

select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
+02"') > interval '1000 hours';

result is false instead of true.
There is a 24 hours error in the age() results

regards

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:

>
> The following bug has been logged online:
>
> Bug reference:      4878
> Logged by:
> Email address:      pamelant@companeo.com
> PostgreSQL version: 8.2.4, 8.3.6
> Operating system:   linux
> Description:        function age() give a wrong interval
> Details:
>
> age() report a wrong interval in some case
>
> example
>
> intervall between the 2 dates is 1008 hours
>
> select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12
> 18:36:05.064066
> +02"') > interval '1000 hours';
>
> result is false instead of true.
> There is a 24 hours error in the age() results
>
> regards
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Le jeudi 25 juin 2009 =C3=A0 11:40 +0200, Frank Heikens a =C3=A9crit :
> select
>     age(
>         '2009-06-23 18:36:05.064066+02' ,
>         '2009-05-12 18:36:05.064066+02') ;
>=20
> Result: "1 mon 11 days"
>=20
> select justify_interval('1000 hours');
>=20
> Result: "1 mon 11 days 16:00:00"
>=20
> select
>     age(
>         '2009-06-23 18:36:05.064066+02' ,
>         '2009-05-12 18:36:05.064066+02')
>     > interval '1000 hours'
> ;
>=20
> Result: false
>=20
> And that's correct, 1 month and 11 days is less than 1 month, 11 days=20=
=20
> and 16 hours, it's not more. This is the actual comparison:
>=20
> select interval '1 mon 11 day' > interval '1 mon 11 day 16 hour';
>=20
> 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=20

regards

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

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

time =3D=3D=3D trouble

Regards,
Frank



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

> Le jeudi 25 juin 2009 =E0 11:40 +0200, Frank Heikens a =E9crit :
>> 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=20=20
> 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
>
>
> --=20
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

"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