Re: possible bug on age() function (8.2.4 , 8.3.6)

On 2009-06-24, Philippe Amelant <pamelant(at)companeo(dot)com> wrote:
> Le mercredi 24 juin 2009 à 12:45 +0000, Jasen Betts a écrit :
>> On 2009-06-24, Philippe Amelant <pamelant(at)companeo(dot)com> wrote:
>> > Ok but if I work with hours or whatever the problem is still there
>> >
>> > SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
>> > EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
>> > EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
>> > 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"') > interval '1007
>> > hours';
>> >
>> > The third test should be true and not false
>>
>> The third test is comparing a double with an interval.
>> compare like with like.
>>
>> SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
>> EXTRACT(EPOCH FROM TIMESTAMP '"2009-05-12 18:36:05.064066+02"'))/3600,
>> EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
>> 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066+02"')
>> > EXTRACT(EPOCH FROM interval '1007 hours');
>>
>
>>From de doc : age(timestamp, timestamp) return an interval so if I
> wrote
> select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
> +02"') > interval '1000 hours';
>
> I think I compare an interval with an interval.

that does but they are different units. one in months and days and the other in hours.

if you want to count days subtract dates,

> the result should be true because there is 1008 hours between the 2
> dates

but there is not reliably 1008 hours in "1 mon 11 days"

if the context of the interval is important apply it,

select timestamptz '2009-05-12 18:36:05.064066+02'
,timestamptz '"2009-06-23 18:36:05.064066+02"'
,interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
< timestamptz '"2009-06-23 18:36:05.064066+02"';

> But I need to substract more than 24 hours to get a 'true'
>
> select age('2009-06-23 18:36:05.064066+02' ,'"2009-05-12 18:36:05.064066
> +02"') > interval '983 hours';

age() works but is not well suited to that use:

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